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

Subquery in FROM (derived table)

The query

SQL
SELECT
  dept_summary.dept_id,
  dept_summary.avg_sal,
  d.dept_name
FROM (
  SELECT dept_id, ROUND(AVG(salary), 2) AS avg_sal
  FROM employees
  GROUP BY dept_id
) AS dept_summary
JOIN departments d ON dept_summary.dept_id = d.dept_id
ORDER BY avg_sal DESC;
Tested against PostgreSQL 16

Note

“Derived table (subquery in FROM) — must be aliased. Useful for pre-aggregating data.

Tables referenced