MySQL - LEFT JOIN

In this tutorial we will learn about left join in MySQL.

When we use the LEFT JOIN, it returns all the rows from the left side table and only matched rows from the right side table. If there is no match at the right side table then we get NULL.

The pictorial representation of LEFT JOIN is given below.

For this tutorial we will be using the employee and comments table.

Employee Table

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Comments Table

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

LEFT JOIN syntax

SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
LEFT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;

LEFT JOIN employee and comments table

In the following example we will list all the employees who commented. For those who not commented will have NULL in their column.

So, for this we will LEFT JOIN the two tables on the column employeeid. This will fetch us all the rows that have matching employeeid in both the tables. If there is no matching employeeid in the right side table i.e., comments table then we will get NULL.

mysql> SELECT e.employeeid, e.firstname, e.lastname, c.commentid, c.commentbody
FROM employee e
LEFT JOIN comments c
ON e.employeeid = c.employeeid
ORDER BY employeeid;

Output:

+------------+-----------+----------+-----------+---------------------+
| employeeid | firstname | lastname | commentid | commentbody         |
+------------+-----------+----------+-----------+---------------------+
| e01        | Yusuf     | Shakeel  |         3 | Happy               |
| e01        | Yusuf     | Shakeel  |         5 | Keep coding....     |
| e02        | John      | Doe      |      NULL | NULL                |
| e03        | Jane      | Doe      |         1 | Awesome             |
| e03        | Jane      | Doe      |         2 | Hello World         |
| e04        | Tin       | Tin      |         4 | This is my comment. |
| e05        | Bob       | Coder    |      NULL | NULL                |
+------------+-----------+----------+-----------+---------------------+
7 rows in set (0.00 sec)