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 WHERE: employees earning above average

The query

SQL
SELECT
  first_name,
  last_name,
  salary,
  ROUND(salary - (SELECT AVG(salary) FROM employees), 2) AS above_avg_by
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
Tested against PostgreSQL 16

Note

“Scalar subquery returns single value. Runs once and reused for each row comparison.

Tables referenced