SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 16 of 18 · ~4 min

Common Table Expressions (CTEs)

Count them up and the "average salary per department" subquery has now been retyped, near-identically, in three separate chapters of this report. A CTE, written with a leading WITH clause, is SQL's answer to that kind of repetition: give a subquery a label up front, and every later part of the same statement can refer to that label as if it were an ordinary table. Nothing about what's computable changes — anything a CTE can do, a sufficiently nested nest of subqueries could technically also do — it's a readability upgrade, not a new capability.

WITH DeptAverages AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY dept
)
SELECT e.name, e.dept, e.salary, d.avg_salary
FROM Employees e
JOIN DeptAverages d ON e.dept = d.dept
WHERE e.salary > d.avg_salary;
-- Bob only — the single employee earning above their own department's average

Only Bob, and it's worth pausing on why: Carol is the IT average (she's the only one of IT's two employees with a salary at all, so the average equals her own number exactly), and Eve is likewise HR's entire average, so neither one is above their own average — they are it. Bob is the only department where two real salaries exist and one of them genuinely beats the mean.

Now try to reuse that CTE for the headcount slide, folding in Finance — and here's the trap that's easy to walk into with a CTE specifically, because the CTE quietly does its GROUP BY before Finance ever gets a chance to exist in the result at all:

WITH DeptAverages AS (
  SELECT dept, AVG(salary) AS avg_salary, COUNT(*) AS headcount
  FROM Employees          -- grouping Employees ALONE — Finance was never a candidate row
  GROUP BY dept
)
SELECT d.dept, da.avg_salary, da.headcount
FROM Departments d
LEFT JOIN DeptAverages da ON d.dept = da.dept;
-- Finance | NULL | NULL   ← headcount is NULL, not 0 — the CTE never produced a Finance row to LEFT JOIN onto

That's a subtly different bug from the ones earlier in this reference: NULL in the headcount column doesn't mean "we checked and found nobody" the way 0 did in chapter one — it means "this row never existed to check in the first place," which is a worse answer for a report, because a NULL on a headcount slide looks like missing data rather than a confirmed zero. The fix is the same lesson from the very first chapter, just moved one level in: do the LEFT JOIN to Departments inside the CTE, before the GROUP BY runs, not after:

WITH DeptAverages AS (
  SELECT d.dept, AVG(e.salary) AS avg_salary, COUNT(e.id) AS headcount
  FROM Departments d
  LEFT JOIN Employees e ON d.dept = e.dept
  GROUP BY d.dept
)
SELECT dept, avg_salary, headcount FROM DeptAverages;
-- Finance | NULL | 0   ← now headcount is a real, checked zero

A single WITH can introduce more than one named block at once, comma-separated, and each later block is free to build on any earlier one — a small pipeline of named steps instead of one wall of nested parentheses. One thing not to assume: that a CTE referenced twice in the outer query only gets computed once. Some optimizers genuinely cache the result the first time and reuse it (called materializing the CTE); others treat the name as pure shorthand and quietly re-run the underlying query at every reference, the same way a view would. Which behavior you get is an engine (and sometimes version) detail — Postgres, for a long stretch of its history, materialized every CTE without exception, a default it has since loosened up.

There's a second flavor worth knowing exists even though nothing in this report needs it: prefix the clause with WITH RECURSIVE and a CTE can reference itself, which is the mechanism behind querying tree- or graph-shaped data — walking every employee under a given manager, say, if this schema had a manager_id column to walk. No fixed number of ordinary joins can express "everyone underneath this person, no matter how many levels down," because you don't know the depth ahead of time. That's a genuinely large topic on its own, deserving more than a paragraph — the useful thing to take away here is just recognizing the shape of problem it solves, so you know to go looking for it later.