MySQL
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.
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)
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)
SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
LEFT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;
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)
ADVERTISEMENT