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.

EasyJOINs (INNER, LEFT, RIGHT, FULL)

LEFT JOIN: all employees even if no department

The query

SQL
SELECT
  e.first_name,
  e.last_name,
  COALESCE(d.dept_name, 'Unassigned') AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
Tested against PostgreSQL 16

Note

“LEFT JOIN keeps ALL rows from left table. COALESCE replaces NULL with a default value.

Tables referenced