Outer Join

An outer join keeps unmatched rows from one or both tables, filling the missing side with NULL. Variants are LEFT, RIGHT, and FULL OUTER JOIN.

A LEFT JOIN returns every row from the left table plus matching rows from the right; where there is no match, the right-side columns are NULL. A FULL OUTER JOIN does this for both sides at once.

Outer joins are ideal for "find records that are missing a relationship" queries — e.g. a LEFT JOIN ... WHERE right.id IS NULL finds employees with no matching department.

Example

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