WITH: multi-step queries you can read

Part of the free LogicWiz SQL & Databases course, Module 12: CTEs and recursive queries. The queries and practice run in the app on real Postgres.

HR asks Meher: "Who earns more than their department's average?" Her first answer is a subquery inside a join inside another subquery. It works, but nobody, including Meher a week later, can read it. What if the query could be written as steps with names?

You'll learn to

• Name a step of a query with WITH ... AS (...)
• Chain several CTEs, each building on the one before
• Know a CTE's scope and when Postgres materialises it
• Rewrite a nested subquery as readable steps

Learn

A CTE is a named subquery

A CTE (Common Table Expression) gives a subquery a name at the top of the statement:

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT ... FROM dept_avg ...;

The main query then uses dept_avg like a table. Nothing new is stored; it is the same derived table you met in subqueries, just written first and named, so the query reads top to bottom like a recipe.

Above their department's average

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, ROUND(d.avg_salary) AS dept_avg
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary
ORDER BY e.salary DESC, e.name;

Step 1 makes one row per department. Step 2 joins every employee to their department's row and keeps those above it. Priya earns more than her own Sales Head, and it shows here.

Chained CTEs: each step uses the last

WITH dept_pay AS (
  SELECT dept_id, COUNT(*) AS staff, SUM(salary) AS payroll
  FROM employees
  GROUP BY dept_id
),
company AS (
  SELECT SUM(payroll) AS total
  FROM dept_pay
)
SELECT d.name, p.staff, p.payroll,
       ROUND(100.0 * p.payroll / c.total, 1) AS pct
FROM dept_pay p
JOIN departments d ON d.dept_id = p.dept_id
CROSS JOIN company c
ORDER BY p.payroll DESC;

One WITH, then CTEs separated by commas. company reads from dept_pay. The intern with no department is in the company total but has no department row to join.

Subquery, CTE, view or temp table?

Scope and speed

A CTE exists only for the one statement it is attached to. After the semicolon it is gone.

Since Postgres 12, a CTE used once is usually inlined: planned as if you had written the subquery in place, so it costs nothing extra. A CTE used twice may be computed once and reused. You can force either: WITH x AS MATERIALIZED (...) or AS NOT MATERIALIZED (...).

So choose CTEs for readability; speed is rarely the reason against them in modern Postgres.

Gone after the semicolon

WITH seniors AS (
  SELECT name FROM employees WHERE salary >= 200000
)
SELECT COUNT(*) FROM seniors;

SELECT * FROM seniors;

The first statement works. The second fails with "relation seniors does not exist", because the CTE belonged only to the first statement.

Only the first CTE gets the word WITH. Writing WITH a AS (...), WITH b AS (...) is a syntax error; it is WITH a AS (...), b AS (...). And there is no comma after the last CTE, right before the main SELECT.

Interviewers ask "CTE vs subquery?" and expect: same power, better readability, can be referenced more than once, and can be recursive (next lesson). Most medium SQL questions are easier as 2-3 named steps; saying your steps aloud while writing them scores well.

MySQL supports WITH (and WITH RECURSIVE) from version 8.0. Older MySQL needs derived tables instead. SQL Server and Oracle use the same WITH syntax.

In one line

WITH name AS (query) names a step for one statement; chain steps with commas and read the query top to bottom.

Interview corner

What is a CTE and why use one over a subquery?

A Common Table Expression is a named subquery defined with WITH at the top of a statement. It does the same job as a derived table but reads top to bottom, can be referenced several times in the statement, and can be recursive.

Is a CTE slower than a subquery in Postgres?

Not since Postgres 12: a CTE referenced once is inlined like a subquery. A CTE referenced several times may be computed once. You can force the choice with AS MATERIALIZED or AS NOT MATERIALIZED. Before version 12, every CTE was an optimisation fence.

CTE vs temp table vs view?

A CTE lives for one statement. A temp table lives for the session, stores rows and can be indexed, good for reusing a heavy intermediate result. A view is saved in the database and reused by every query.