A LEFT JOIN statement retrieves all rows from the left table and includes matching rows from the right table based on the join condition. Left join is categorized as an outer join and is also known as left outer join. Other variations of outer joins include right join and full join.
The following Venn diagram illustrates how a left join functions:
Note: An outer join is a type of join that includes rows in the result set even when there is no matching row found between the joined tables.
To understand this clearly, let's examine 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 |
Suppose you want to fetch the id, name, and hire date of all employees along with their department names, regardless of whether they are assigned to any department. To achieve this result, you can use a left join.
The following SQL statement retrieves the employee's id, name, hiring date, and their department name by joining the employees and departments tables based on the common dept_id field. It includes all employees, even those who are not assigned to a department.
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: When writing a join query, the left table is the one that appears on the left side of the JOIN clause, and the right table is the one that appears on the right side.
After running the command mentioned earlier, the result will look similar to 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 | +--------+--------------+------------+-----------------+
In a left join, all rows from the employees table are included in the result set, regardless of whether there is a matching dept_id in the departments table.
Note: In a left join, if a row from the left table has no matching row in the right table, the resulting row will have NULL values for all columns from the right table.