SQL WITH Clause for creating Common Table Expressions

Learn how to use SQL CTEs (Common Table Expressions) with the WITH clause to simplify complex queries, improve readability, and avoid nested subqueries.

As your SQL queries grow in complexity, they can quickly become deeply nested, hard to read, and painful to debug. Common Table Expressions (CTEs) solve this problem by letting you define temporary, named result sets that exist only for the duration of a single query. If you have ever written a subquery inside a subquery inside yet another subquery, the SQL CTE is about to become your best friend.

This guide covers everything you need to know about the SQL WITH clause: how to write CTEs, how to chain multiple CTEs together, when to use them instead of subqueries, and the common pitfalls to avoid. Every concept is backed by practical examples with clear outputs so you can start using CTEs immediately.

What Is a CTE?

A Common Table Expression (CTE) is a temporary, named result set defined at the beginning of a SQL statement using the WITH keyword. It only exists during the execution of that single query. Think of it as creating a temporary view that disappears the moment the query finishes.

CTEs are supported by all major relational databases: PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.8.3+, and MariaDB 10.2.1+.

Why CTEs Matter

Readability: Break complex logic into named, self-documenting steps
Maintainability: Change one CTE definition instead of hunting through nested subqueries
Reusability: Reference the same CTE multiple times in one query without duplicating code
Debugging: Test each CTE independently by running it as a standalone query
Recursion: Enable recursive queries (hierarchical data traversal) that are impossible with simple subqueries

The Sample Data

We will use the following tables throughout this guide:

departments table:

id	name
1	Engineering
2	Sales
3	Marketing

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
INSERT INTO departments (id, name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'Marketing');

employees table:

id	name	department_id	salary	hire_date
1	Alice	1	95000	2020-03-15
2	Bob	1	88000	2021-06-01
3	Charlie	2	72000	2019-11-20
4	Diana	2	68000	2022-01-10
5	Eve	3	74000	2020-08-05
6	Frank	3	70000	2023-02-14

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
);
INSERT INTO employees (id, name, department_id, salary, hire_date) VALUES
(1, 'Alice',   1, 95000, '2020-03-15'),
(2, 'Bob',     1, 88000, '2021-06-01'),
(3, 'Charlie', 2, 72000, '2019-11-20'),
(4, 'Diana',   2, 68000, '2022-01-10'),
(5, 'Eve',     3, 74000, '2020-08-05'),
(6, 'Frank',   3, 70000, '2023-02-14');

orders table:

id	customer_id	order_date	amount
1	101	2024-01-05	250
2	102	2024-01-08	430
3	101	2024-01-12	180
4	103	2024-01-15	520
5	102	2024-01-20	310
6	101	2024-02-02	275
7	103	2024-02-10	190
8	104	2024-02-15	640
9	102	2024-02-22	380
10	104	2024-03-01	450

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);
INSERT INTO orders (id, customer_id, order_date, amount) VALUES
(1, 101, '2024-01-05', 250),
(2, 102, '2024-01-08', 430),
(3, 101, '2024-01-12', 180),
(4, 103, '2024-01-15', 520),
(5, 102, '2024-01-20', 310),
(6, 101, '2024-02-02', 275),
(7, 103, '2024-02-10', 190),
(8, 104, '2024-02-15', 640),
(9, 102, '2024-02-22', 380),
(10,104, '2024-03-01', 450);

CTE Syntax

Basic Structure

WITH cte_name AS (
    -- Any valid SELECT query
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
SELECT *
FROM cte_name;

The WITH keyword introduces the CTE. You give it a name, define its query inside parentheses, and then reference that name in the main query as if it were a regular table.

Your First CTE

Let's start with a simple example. Instead of putting a subquery in the FROM clause, we move it into a CTE:

WITH high_earners AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 75000
)
SELECT *
FROM high_earners;

Output:

name	salary
Alice	95000
Bob	88000

This is deliberately simple to show the structure. The real value becomes apparent with more complex queries.

CTE with Column Aliases

You can optionally specify column names for the CTE directly after the CTE name:

WITH dept_stats (department_id, avg_salary, headcount) AS (
    SELECT
        department_id,
        ROUND(AVG(salary), 2),
        COUNT(*)
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM dept_stats;

Output:

department_id	avg_salary	headcount
1	91500.00	2
2	70000.00	2
3	72000.00	2

The column aliases (department_id, avg_salary, headcount) override whatever the inner query's columns are named. This is optional but can improve clarity when the inner query uses expressions without aliases.

CTE with JOINs and Filtering

CTEs truly shine when they simplify a query that involves joins, aggregations, and filtering in combination. Consider this question: "Show each employee alongside their department's average salary, but only for employees who earn above their department's average."

Without a CTE (Subquery Approach)

SELECT
    e.name,
    d.name AS department,
    e.salary,
    dept_avg.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
    SELECT department_id, ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

name	department	salary	avg_salary
Alice	Engineering	95000.00	91500.00
Charlie	Sales	72000.00	70000.00
Eve	Marketing	74000.00	72000.00

With a CTE

WITH dept_averages AS (
    SELECT
        department_id,
        ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name,
    d.name AS department,
    e.salary,
    da.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN dept_averages da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary;

name	department	salary	avg_salary
Alice	Engineering	95000.00	91500.00
Charlie	Sales	72000.00	70000.00
Eve	Marketing	74000.00	72000.00

Both queries produce the same output, but the CTE version is easier to read. The aggregation logic is cleanly separated into dept_averages, and the main query reads almost like plain English: "join employees with departments and department averages, then filter."

Multiple CTEs in One Query

One of the most powerful features of the SQL WITH clause is the ability to define multiple CTEs in a single query. Each CTE can reference the ones defined before it, creating a step-by-step data pipeline.

Syntax

WITH
    cte_first AS (
        SELECT ...
    ),
    cte_second AS (
        SELECT ...
        FROM cte_first   -- Can reference cte_first
    ),
    cte_third AS (
        SELECT ...
        FROM cte_second  -- Can reference cte_first and cte_second
    )
SELECT *
FROM cte_third;

Notice:

Only one WITH keyword at the beginning
CTEs are separated by commas
Each subsequent CTE can reference any CTE defined above it
The final SELECT can reference any of the CTEs

Practical Example: Customer Order Analysis

Let's answer a multi-step question: "For each customer, show their total spending, their spending rank, and whether they are above or below the overall average."

WITH customer_totals AS (
    -- Step 1: Calculate total spending per customer
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
overall_stats AS (
    -- Step 2: Calculate the overall average from Step 1
    SELECT
        ROUND(AVG(total_spent), 2) AS avg_spending
    FROM customer_totals
),
ranked_customers AS (
    -- Step 3: Rank customers and compare to average
    SELECT
        ct.customer_id,
        ct.order_count,
        ct.total_spent,
        os.avg_spending,
        RANK() OVER (ORDER BY ct.total_spent DESC) AS spending_rank,
        CASE
            WHEN ct.total_spent > os.avg_spending THEN 'Above Average'
            WHEN ct.total_spent < os.avg_spending THEN 'Below Average'
            ELSE 'Average'
        END AS spending_category
    FROM customer_totals ct
    CROSS JOIN overall_stats os
)
SELECT *
FROM ranked_customers
ORDER BY spending_rank;

Output:

customer_id	order_count	total_spent	avg_spending	spending_rank	spending_category
102	3	1120.00	906.25	1	Above Average
104	2	1090.00	906.25	2	Above Average
103	2	710.00	906.25	3	Below Average
101	3	705.00	906.25	4	Below Average

Let's break down what each CTE does:

customer_totals: Aggregates orders to get each customer's total spending and order count.
overall_stats: Computes the average spending across all customers (from the already-aggregated data in customer_totals).
ranked_customers: Joins the first two CTEs, adds a rank, and categorizes each customer relative to the average.

Each step is self-contained, named, and easy to understand. You could debug any step by running just that CTE as a standalone query.

Debugging Individual CTEs

To test a specific CTE in isolation, temporarily replace the final query with a simple SELECT * FROM cte_name:

WITH customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
-- Temporarily select from this CTE to verify its output
SELECT * FROM customer_totals;

Once satisfied, add the next CTE and repeat.

Referencing a CTE Multiple Times

Unlike subqueries, a CTE can be referenced multiple times in the same query. This avoids duplicating logic and ensures consistency:

WITH dept_salary AS (
    SELECT
        department_id,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    d1.department_id AS dept_1,
    d1.total_salary AS salary_1,
    d2.department_id AS dept_2,
    d2.total_salary AS salary_2,
    d1.total_salary - d2.total_salary AS difference
FROM dept_salary d1
CROSS JOIN dept_salary d2
WHERE d1.department_id < d2.department_id
ORDER BY d1.department_id, d2.department_id;

Output:

dept_1	salary_1	dept_2	salary_2	difference
1	183000.00	2	140000.00	43000.00
1	183000.00	3	144000.00	39000.00
2	140000.00	3	144000.00	-4000.00

The dept_salary CTE is referenced twice (as d1 and d2) to compare every pair of departments. With a subquery, you would have to write the same aggregation logic twice.

CTEs vs Subqueries: Readability and Maintainability

Both CTEs and subqueries can solve the same problems. The difference lies in how you write and maintain the code. Let's compare them directly.

The Same Query: Subquery Version

"Find employees whose salary is above their department's average."

SELECT
    e.name,
    e.salary,
    e.department_id,
    da.avg_salary
FROM employees e
JOIN (
    SELECT
        department_id,
        ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department_id
) da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY e.salary DESC;

name	salary	department_id	avg_salary
Alice	95000.00	1	91500.00
Eve	74000.00	3	72000.00
Charlie	72000.00	2	70000.00

The Same Query: CTE Version

"Find employees whose salary is above their department's average."

WITH dept_averages AS (
    SELECT
        department_id,
        ROUND(AVG(salary), 2) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name,
    e.salary,
    e.department_id,
    da.avg_salary
FROM employees e
JOIN dept_averages da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY e.salary DESC;

name	salary	department_id	avg_salary
Alice	95000.00	1	91500.00
Eve	74000.00	3	72000.00
Charlie	72000.00	2	70000.00

Detailed Comparison

Aspect	Subquery	CTE
Readability	Can become deeply nested and hard to follow	Flat, top-down structure with named steps
Reusability	Must be duplicated if needed multiple times	Defined once, referenced as many times as needed
Debugging	Must extract and test nested parts manually	Each CTE can be run independently
Naming	Anonymous (no name unless aliased)	Named, self-documenting
Recursion	Not possible	Supported via recursive CTEs
Performance	Usually identical to CTE	Usually identical to subquery
Scope	Exists only within the clause it appears in	Available to the entire main query

When Subqueries Are Fine

For simple, one-level operations, subqueries can be perfectly readable:

-- Simple and clear: no need for a CTE
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

There is no reason to force a CTE here. The subquery is short, self-contained, and easy to understand.

When CTEs Are Better

As soon as you have two or more levels of derived logic, CTEs become significantly better:

Deeply Nested Subquery (Hard to Read)

SELECT
    customer_id,
    total_spent,
    spending_rank
FROM (
    SELECT
        customer_id,
        total_spent,
        RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_spent
        FROM orders
        GROUP BY customer_id
    ) customer_totals
) ranked_customers
WHERE spending_rank <= 3;

This works, but the three levels of nesting force you to read from the inside out. Adding another layer of logic would make it even harder to follow.

The same query with CTEs:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT
        customer_id,
        total_spent,
        RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
    FROM customer_totals
)
SELECT customer_id, total_spent, spending_rank
FROM ranked_customers
WHERE spending_rank <= 3;

Output:

customer_id	total_spent	spending_rank
102	1120.00	1
104	1090.00	2
103	710.00	3

The CTE version reads top-to-bottom like a recipe: first calculate totals, then rank them, then pick the top 3. No nesting, no inside-out reading.

CTEs with INSERT, UPDATE, and DELETE

CTEs are not limited to SELECT statements. In many databases, you can use them with data modification statements.

CTE with DELETE

Delete customers who have spent below average:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
below_avg_customers AS (
    SELECT customer_id
    FROM customer_totals
    WHERE total_spent < (SELECT AVG(total_spent) FROM customer_totals)
)
DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM below_avg_customers);

CTE with UPDATE (SQL Server / PostgreSQL)

WITH dept_averages AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
UPDATE employees
SET salary = salary * 1.05
WHERE department_id IN (
    SELECT department_id
    FROM dept_averages
    WHERE avg_salary < 72000
);

This gives a 5% raise to all employees in departments whose average salary is below 72000.

CTEs with Window Functions

CTEs and window functions are natural partners. Since window functions cannot be used in WHERE, you often need an intermediate step, and a CTE is the cleanest way to provide one.

Example: Filter by Window Function Result

Find the highest-paid employee in each department:

WITH ranked AS (
    SELECT
        e.name,
        d.name AS department,
        e.salary,
        ROW_NUMBER() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS rn
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

Output:

name	department	salary
Alice	Engineering	95000.00
Charlie	Sales	72000.00
Eve	Marketing	74000.00

Without the CTE, you would need a subquery in the FROM clause. The CTE makes the intent immediately clear: rank employees, then pick the top one per department.

Example: Running Total with Threshold

Find the point at which cumulative order amounts exceed 1000 for each customer:

WITH running AS (
    SELECT
        customer_id,
        order_date,
        amount,
        SUM(amount) OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) AS cumulative_total
    FROM orders
)
SELECT
    customer_id,
    order_date,
    amount,
    cumulative_total
FROM running
WHERE cumulative_total >= 1000;

Output:

customer_id	order_date	amount	cumulative_total
102	2024-02-22	380.00	1120.00
104	2024-03-01	450.00	1090.00

Common Mistakes to Avoid

Mistake 1: Using Multiple WITH Keywords

Each query can only have one WITH keyword. Multiple CTEs are separated by commas, not by additional WITH keywords:

-- WRONG: Multiple WITH keywords
WITH cte_one AS (
    SELECT * FROM employees
)
WITH cte_two AS (     -- ERROR!
    SELECT * FROM orders
)
SELECT * FROM cte_one;

Error: Syntax error near WITH

Fix: Use a single WITH and separate CTEs with commas:

-- CORRECT: One WITH, comma-separated CTEs
WITH cte_one AS (
    SELECT * FROM employees
),
cte_two AS (
    SELECT * FROM orders
)
SELECT * FROM cte_one;

Mistake 2: Referencing a CTE Defined Later

CTEs can only reference CTEs that are defined before them. Forward references are not allowed:

-- WRONG: cte_second tries to reference cte_third, which hasn't been defined yet
WITH cte_second AS (
    SELECT * FROM cte_third  -- ERROR: cte_third doesn't exist yet
),
cte_third AS (
    SELECT * FROM employees
)
SELECT * FROM cte_second;

Fix: Reorder the CTEs so dependencies come first:

-- CORRECT: cte_third is defined first
WITH cte_third AS (
    SELECT * FROM employees
),
cte_second AS (
    SELECT * FROM cte_third  -- Now this works
)
SELECT * FROM cte_second;

Mistake 3: Treating CTEs as Persistent Objects

CTEs exist only for the duration of the single statement they are defined in. You cannot define a CTE in one query and reference it in a separate query:

-- Query 1: Define CTE
WITH my_cte AS (
    SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM my_cte;

-- Query 2: Try to reuse CTE (FAILS)
SELECT * FROM my_cte;  -- ERROR: relation "my_cte" does not exist

If you need a reusable named query, consider a view or a temporary table instead.

Mistake 4: Unnecessary CTEs for Simple Queries

Not every query benefits from a CTE. Using one for trivially simple operations adds verbosity without improving readability:

-- Overkill: CTE adds no value here
WITH active_employees AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT * FROM active_employees;

-- Just write it directly
SELECT name, salary
FROM employees
WHERE salary > 70000;

CTE Performance Considerations

A common question is whether CTEs are faster or slower than subqueries. The answer depends on the database engine.

How Databases Handle CTEs

Database	CTE Behavior
PostgreSQL 12+	CTEs are inlined (optimized like subqueries) by default. Use `MATERIALIZED` keyword to force materialization if needed.
PostgreSQL < 12	CTEs are materialized (computed once, stored in memory). This can be slower if the optimizer could have pushed predicates into the CTE.
MySQL 8+	CTEs are generally inlined and optimized like derived tables.
SQL Server	CTEs are inlined. They are expanded into the query plan like subqueries.
Oracle	CTEs may be materialized if referenced multiple times. Use `/+ MATERIALIZE /` or `/+ INLINE /` hints for control.

Materialization: When It Helps and Hurts

Materialization means the CTE is computed once and its results are stored temporarily. This helps when the same CTE is referenced multiple times in the query, because the computation only happens once. It hurts when the optimizer could have pushed a filter from the outer query into the CTE to reduce the amount of data processed early.

-- PostgreSQL 12+: Force materialization when referencing the CTE multiple times
WITH dept_stats AS MATERIALIZED (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM dept_stats d1
CROSS JOIN dept_stats d2;

-- PostgreSQL 12+: Force inlining for better filter pushdown
WITH filtered_employees AS NOT MATERIALIZED (
    SELECT * FROM employees
)
SELECT * FROM filtered_employees
WHERE salary > 80000;  -- This filter can be pushed into the CTE

Real-World Pattern: Multi-Step Report

Here is a comprehensive example that combines multiple CTEs, joins, window functions, and aggregations to build a monthly order report:

WITH monthly_orders AS (
    -- Step 1: Aggregate orders by customer and month
    SELECT
        customer_id,
        DATE_FORMAT(order_date, '%Y-%m-01') AS order_month,  -- first day of month (MySQL)
        COUNT(*) AS num_orders,
        SUM(amount) AS monthly_total
    FROM orders
    GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m-01')
),
with_prev_month AS (
    -- Step 2: Add previous month's total for comparison
    SELECT
        customer_id,
        order_month,
        num_orders,
        monthly_total,
        LAG(monthly_total) OVER (
            PARTITION BY customer_id
            ORDER BY order_month
        ) AS prev_month_total
    FROM monthly_orders
),
final_report AS (
    -- Step 3: Calculate month-over-month change
    SELECT
        customer_id,
        order_month,
        num_orders,
        monthly_total,
        prev_month_total,
        monthly_total - prev_month_total AS mom_change,
        CASE
            WHEN prev_month_total IS NULL THEN 'First Month'
            WHEN monthly_total > prev_month_total THEN 'Growth'
            WHEN monthly_total < prev_month_total THEN 'Decline'
            ELSE 'Flat'
        END AS trend
    FROM with_prev_month
)
SELECT *
FROM final_report
ORDER BY customer_id, order_month;

customer_id	order_month	num_orders	monthly_total	prev_month_total	mom_change	trend
101	2024-01-01	2	430.00	NULL	NULL	First Month
101	2024-02-01	1	275.00	430.00	-155.00	Decline
102	2024-01-01	2	740.00	NULL	NULL	First Month
102	2024-02-01	1	380.00	740.00	-360.00	Decline
103	2024-01-01	1	520.00	NULL	NULL	First Month
103	2024-02-01	1	190.00	520.00	-330.00	Decline
104	2024-02-01	1	640.00	NULL	NULL	First Month
104	2024-03-01	1	450.00	640.00	-190.00	Decline

Each CTE handles one logical step: aggregate, compare, label. The final SELECT just reads from the finished report. Adding a new step (like a percentage change or rank) means adding one more CTE rather than nesting another level of subqueries.

Summary

The SQL CTE (Common Table Expression) introduced by the SQL WITH clause is one of the most important tools for writing clean, maintainable SQL:

CTEs are temporary, named result sets defined with WITH that exist only for a single query.
Multiple CTEs can be chained in one query by separating them with commas. Each CTE can reference any CTE defined above it.
CTEs vs subqueries: Both produce the same results, but CTEs offer better readability, reusability, and debuggability, especially for multi-step or complex queries.
A CTE can be referenced multiple times in the same query, eliminating duplicated subquery logic.
CTEs work with SELECT, INSERT, UPDATE, and DELETE statements.
CTEs are the cleanest way to filter on window function results, since window functions cannot appear in WHERE.
Performance is generally equivalent to subqueries. Only use materialization hints when you have measured a specific issue.