Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateJOINs (INNER, LEFT, RIGHT, FULL)

FULL OUTER JOIN: all employees and departments

The query

SQL
SELECT
  e.first_name,
  e.last_name,
  d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL; -- only unmatched rows
Tested against PostgreSQL 16

Note

“FULL OUTER JOIN returns ALL rows from BOTH tables, including unmatched ones.

Tables referenced