LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right table.

Source data — Pattern 6: Joins

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces the departments table. Topic 38 also needs a projects table, while topic 39 introduces a salary_grades table for non-equi joins.

LEFT JOIN — source table 1

LEFT JOIN — source table 2

LEFT JOIN — source table 3

LEFT JOIN — source table 4

What it does

LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, the right-side columns return NULL.

Problem

Display all employees, including those without a department.

SELECT e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Result

LEFT JOIN — result 1