A left join is a table join in which all of the matched records from the table on the left side of the join are returned, regardless of whether a matching record is found in the table on the right side of the join.
To illustrate the workings of a left join, let’s consider two tables representing employees and departments within a fictional company.
Table: employees
| EmpId | DeptId | EmpFirstName | EmpLastName |
|---|---|---|---|
| 1 | 3 | John | Doe |
| 2 | 1 | Jane | Doe |
| 3 | 2 | Jim | Smith |
| 4 | null | Joe | Schmoe |
Table: departments
| DeptId | Department |
|---|---|
| 1 | Human Resources |
| 2 | Accounting |
| 3 | Information Technology |
The Situation
Here’s a situation that would require a left join: design a query that produces a list of all employees, including which department, if any, each employee works for.
Joe Schmoe is the monkey wrench in this case. He doesn’t work for any department, but he must be included in the results of our query. A regular join would exclude Joe from the results — but a left join would do the trick.
The Code
SELECT
employees.EmpFirstName,
employees.EmpLastName,
departments.Department
FROM
employees
LEFT JOIN departments
ON employees.DeptId = departments.DeptId
The Output
| EmpFirstName | EmpLastName | Department |
|---|---|---|
| John | Doe | Information Technology |
| Jane | Doe | Human Resources |
| Jim | Smith | Accounting |
| Joe | Schmoe | null |
