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.

AdvancedSubqueries & EXISTS

Multi-level nested subquery

The query

SQL
SELECT
  first_name, last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE budget > (
      SELECT AVG(budget) FROM departments
    )
  )
);
Tested against PostgreSQL 16

Note

“3-level nested subquery. Generally refactor with CTEs for readability and maintenance.

Tables referenced