FULL OUTER JOIN

FULL OUTER JOIN returns every row from both tables.

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.

FULL OUTER JOIN — source table 1

FULL OUTER JOIN — source table 2

FULL OUTER JOIN — source table 3

FULL OUTER JOIN — source table 4

What it does

FULL OUTER JOIN returns every row from both tables. Matching rows are combined, while non-matching rows contain NULL values.

Problem

Display all employees and departments.

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

Result

FULL OUTER JOIN — result 1

SQL · Pattern 6 — Joins