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.

EasySubqueries & EXISTS

IN with subquery: employees in engineering departments

The query

SQL
SELECT first_name, last_name, job_title
FROM employees
WHERE dept_id IN (
  SELECT dept_id
  FROM departments
  WHERE dept_name ILIKE '%engineering%' OR dept_name ILIKE '%tech%'
)
ORDER BY last_name;
Tested against PostgreSQL 16

Note

“IN with subquery is clean and readable. For large subqueries, JOIN may perform better.

Tables referenced