SQL Common Table Expressions (WITH / CTE) Explained

A Common Table Expression (CTE) is a named, temporary result set that you define with a WITH clause right before your main query. Think of it as giving a subquery a name and pulling it up to the top of your statement, where it's easier to read and, if needed, reference more than once.

CTEs don't let you do anything a subquery technically couldn't — but once a query has more than one layer of nesting, a subquery buried inside WHERE or FROM gets hard to follow. A CTE turns that mess into a sequence of clearly named steps.

Basic CTE Syntax

WITH high_earners AS (
    SELECT name, department, salary
    FROM employees
    WHERE salary > 70000
)
SELECT department, COUNT(*) AS high_earner_count
FROM high_earners
GROUP BY department;

Result:

department | high_earner_count
-------------|------------------
Engineering | 4
Sales | 2

The WITH high_earners AS (...) block runs first, builds a temporary named result, and then the main SELECT below it queries that result as if it were a real table. Once high_earners is defined, you can reference it as many times as you like in the rest of the statement — a subquery would force you to repeat the whole thing.

Rewriting a Subquery as a CTE

Here's the same logic written as a nested subquery first, then as a CTE, so you can compare them directly:

-- Subquery version
SELECT department, AVG(salary) AS avg_salary
FROM (
    SELECT department, salary
    FROM employees
    WHERE hire_date >= '2023-01-01'
) AS recent_hires
GROUP BY department;

-- CTE version — same result, easier to read
WITH recent_hires AS (
    SELECT department, salary
    FROM employees
    WHERE hire_date >= '2023-01-01'
)
SELECT department, AVG(salary) AS avg_salary
FROM recent_hires
GROUP BY department;

Both versions return exactly the same rows. The CTE version just reads top-to-bottom instead of inside-out, which matters a lot once you're combining several filtered, grouped, or joined steps in one query. You can even chain multiple CTEs, separated by commas, each one able to reference the ones defined before it.

Multiple CTEs in One Query

WITH dept_totals AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
),
top_departments AS (
    SELECT department
    FROM dept_totals
    WHERE total_salary > 200000
)
SELECT * FROM top_departments;

Result:

department
-------------
Engineering

Here top_departments builds directly on dept_totals — something that's awkward to express as nested subqueries but reads naturally as a short sequence of named steps.

CTEs are especially useful once you start combining GROUP BY aggregation with further filtering or joins, and they pair well with a plain subquery for the cases where a single nested step is simpler than naming one.