IntermediateSubqueries & EXISTS

Correlated subquery: find department average for each employee

The query

SQL

SELECT
  first_name,
  last_name,
  salary,
  dept_id,
  (SELECT ROUND(AVG(e2.salary), 2)
   FROM employees e2
   WHERE e2.dept_id = e1.dept_id) AS dept_avg_salary
FROM employees e1
ORDER BY dept_id, salary DESC;

Tested against PostgreSQL 16

Note

“Correlated subquery references outer query. Runs once PER ROW — can be slow on large tables.

Tables referenced

employees10 cols