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)

Employees NOT assigned to any project (anti-join)

The query

SQL
SELECT e.first_name, e.last_name, e.job_title
FROM employees e
LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
WHERE pa.assign_id IS NULL; -- Unmatched = no projects
Tested against PostgreSQL 16

Note

“Anti-join pattern: LEFT JOIN + WHERE IS NULL finds records with no matching row.

Tables referenced