SQL Left Join

In this tutorial you will learn how to retrieve data from two tables using SQL left join.

Using Left Joins
A LEFT JOIN statement returns all rows from the left table along with the rows from the right table for which the join condition is met. Left join is a type of outer join that's why it is also referred as left outer join. Other variations of outer join are right join and full join.

The following Venn diagram illustrates how left join works.

![SQL Left Join Illustration](/uploads/images/20240424/a07aa8ba6931947342fe22e711b1ddf2.png "SQL Left Join Illustration")

Note: An outer join is a join that includes rows in a result set even though there may not be a match between rows in the two tables being joined.

To understand this clearly, let's look at the following employees and departments tables.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+
 	
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
Table: employees		Table: departments
Now, let's say you want to retrieve the id, name and hire date of all the employees along with the name of their department, irrespective of whether they are assigned to any department or not. To get such type of result set we need to apply a left join.

The following statement retrieves employee's id, name, hiring date and their department name by joining the employees and departments tables together using the common dept_id field. It also includes those employees who are not assigned to a department.

ExampleTry this code »
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
Tip: In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

After executing the above command, you'll get the output something like this:

+--------+--------------+------------+-----------------+
| emp_id | emp_name     | hire_date  | dept_name       |
+--------+--------------+------------+-----------------+
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources |
|      2 | Tony Montana | 2002-07-15 | Administration  |
|      3 | Sarah Connor | 2005-10-18 | Sales           |
|      4 | Rick Deckard | 2007-01-03 | Finance         |
|      5 | Martin Blank | 2008-06-24 | NULL            |
+--------+--------------+------------+-----------------+
As you can clearly see the left join includes all the rows from the employees table in the result set, whether or not there is a match on the dept_id column in the departments table.

Note: If there is a row in the left table but no match in the right table, then the associated result row contains NULL values for all columns coming from the right table.