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.
NULL
The pictorial representation of LEFT JOIN is given below.
For this tutorial we will be using the employee and comments table.
employee
comments
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.
employeeid
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)