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.

IntermediateSubqueries & EXISTS

Find departments with no active projects

The query

SQL
SELECT dept_name, location
FROM departments
WHERE dept_id NOT IN (
  SELECT DISTINCT dept_id
  FROM projects
  WHERE status = 'active'
    AND dept_id IS NOT NULL
)
ORDER BY dept_name;
Tested against PostgreSQL 16

Note

“NOT IN with subquery for exclusion. IMPORTANT: add IS NOT NULL in subquery to avoid NOT IN NULL trap.

Tables referenced