Simple CTEs with WITH

As queries grow, nested subqueries turn into parentheses soup. A subquery three levels deep is hard to read, hard to debug, and easy to break when you come back to it next month.

A Common Table Expression — a CTE — pulls a subquery out of the middle of your query and gives it a name. You define it once at the top with WITH, then reference that name as if it were a table. The result is the same data, but the query reads top-to-bottom instead of inside-out.

CTEs do not create permanent tables, indexes, or storage. They are visible only for the single query that follows them, and the database discards them as soon as the query finishes. Think of a CTE as a temporary view that exists for one question.

flowchart TD
  subgraph OLD["Nested subquery style: read inside-out"]
    O1["SELECT ... FROM ("] --> O2["SELECT ... FROM ("]
    O2 --> O3["innermost SELECT<br/>then close both parens"]
  end
  subgraph NEW["CTE style: read top-down"]
    W["WITH named AS (SELECT ...)"] --> M["SELECT ... FROM named"]
  end
  style NEW fill:#0e7490,color:#fff
  style OLD fill:#1e293b,color:#fff

A CTE lifts a nested subquery into a named block that the main query can reference cleanly.

The WITH syntax

A CTE starts with the keyword WITH, followed by a name, the keyword AS, and a parenthesised SELECT statement. The main query follows immediately after.

WITH company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.name, e.salary
FROM employees e, company_avg c
WHERE e.salary > c.avg_sal;

Here company_avg is the CTE name, and the main query treats it like a one-row table. Because the average is computed once and named, the WHERE clause reads almost like English: 'where the employee's salary is greater than the company average.' No hunting through nested parentheses to find where the average came from.

A CTE computes the company average, and the main query filters against it like a table.

WITH company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.name, e.salary
FROM employees e, company_avg c
WHERE e.salary > c.avg_sal;

CTEs versus subqueries: when to prefer which

Both approaches return identical results, but CTEs win on readability whenever the same logic is used more than once or when the subquery is long enough to need its own line breaks. A CTE also lets you build the query in stages: define the building block, verify it mentally, then use it.

Subqueries still have their place. A trivial one-line scalar subquery inside a WHERE clause is often cleaner than hauling it up to a WITH clause. The guiding principle is: if naming the subquery would make the main query easier to follow, use a CTE. If the subquery is a simple single-value lookup, keep it inline.

One practical advantage of CTEs is composition. Because the CTE is named, you can reference it multiple times in the main query — in a JOIN, in a WHERE, and in the SELECT list — without copying and pasting the same subquery text.

The same CTE joined to employees to show both above-average and below-average earners in one query.

WITH company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.name, e.salary,
  CASE WHEN e.salary > c.avg_sal THEN 'above' ELSE 'below' END AS vs_avg
FROM employees e
CROSS JOIN company_avg c;

flowchart LR
  CTE["WITH stats AS (...)"] --> J1["JOIN stats ON ..."]
  CTE --> J2["WHERE col > stats.avg"]
  CTE --> J3["SELECT stats.avg AS reference"]
  style CTE fill:#0e7490,color:#fff

A CTE can be referenced multiple times in the main query without duplicating its definition.

flowchart LR
  W["WITH cte AS (...)"] --> Q1["SELECT ... FROM cte"]
  W -.->|"invisible"| Q2["SELECT ... FROM cte"]
  style W fill:#0e7490,color:#fff
  style Q1 fill:#0e7490,color:#fff
  style Q2 fill:#b45309,color:#fff

A CTE lives only for the query that immediately follows its definition.

Building readable pipelines

The real power of CTEs emerges when you chain them. A complex analysis can be decomposed into simple stages: first compute averages, then rank departments, then filter to the top tier. Each stage is a CTE, and the final query is a short, readable composition of named blocks.

Even with a single CTE, the discipline of naming your intermediate results forces you to think in stages. 'What am I actually computing here?' If you cannot give the CTE a clear name, the logic itself may be unclear. That naming step is a surprisingly effective design tool.

Exercise

Define a CTE named dept_avg that computes the average salary per department from the employees table. Then join it to departments and return only rows where the average is strictly greater than 600000. Show the department name and avg_sal.

WITH dept_avg AS (
  -- compute avg salary per department
)
SELECT d.name, da.avg_sal
FROM dept_avg da
JOIN departments d ON da.department_id = d.id
WHERE da.avg_sal > 600000;

WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) SELECT d.name, da.avg_sal FROM dept_avg da JOIN departments d ON da.department_id = d.id WHERE da.avg_sal > 600000;

Exercise

Define a CTE named company_avg that holds the company-wide average salary. Then return the name and salary of every employee who earns more than that average.

WITH company_avg AS (
  -- compute overall average
)
SELECT e.name, e.salary
FROM employees e, company_avg c
WHERE -- compare salary to average
;

WITH company_avg AS (SELECT AVG(salary) AS avg_sal FROM employees) SELECT e.name, e.salary FROM employees e, company_avg c WHERE e.salary > c.avg_sal;

Exercise

This query defines a CTE called dept_stats but crashes because the main query references it with a table alias it never declared. Fix the FROM clause.

WITH dept_stats AS (
  SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT d.name, ds.avg_sal, ds.headcount
FROM dept_stats
JOIN departments d ON ds.department_id = d.id;

WITH dept_stats AS (SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS headcount FROM employees GROUP BY department_id) SELECT d.name, ds.avg_sal, ds.headcount FROM dept_stats ds JOIN departments d ON ds.department_id = d.id;

Exercise

Define a CTE named oslo_depts that selects the id of every department with city = 'Oslo'. Then find all employees in those departments who earn more than 500000. Return the employee name and salary.

WITH oslo_depts AS (
  -- select Oslo department ids
)
SELECT e.name, e.salary
FROM employees e
JOIN oslo_depts o ON e.department_id = o.id
WHERE e.salary > 500000;

WITH oslo_depts AS (SELECT id FROM departments WHERE city = 'Oslo') SELECT e.name, e.salary FROM employees e JOIN oslo_depts o ON e.department_id = o.id WHERE e.salary > 500000;

Exercise

How long does a CTE defined with WITH survive?

Recap

A CTE is a named subquery defined at the top of a statement with WITH ... AS (...).
The main query references the CTE like a table, making complex logic readable.
CTEs improve clarity most when the same subquery is used multiple times or when the logic is long enough to deserve its own name.
CTEs are query-scoped — they vanish after the single SELECT that follows them.

Next you will learn how to make CTEs recursive, so a query can feed its own output back into itself to generate sequences or walk hierarchies.

The WITH syntax

CTEs versus subqueries: when to prefer which

Building readable pipelines

Recap

Checkpoint quiz

Go deeper — technical resources