SQLAdvanced SQL Queriesadvanced40 min read

Common Table Expressions (CTEs)

Write readable, modular SQL queries using Common Table Expressions (CTEs), chain query stages, and understand PostgreSQL execution behavior.

On This Page (Table of Contents)

In this lesson, you will learn

✓Structure multi-stage queries using WITH cte_name AS (...).
✓Replace difficult-to-read nested subqueries with clearly named CTEs.
✓Chain multiple CTEs together in a single statement.
✓Use CTEs with filtering, aggregation, joins, and window functions.
✓Understand when a CTE can be referenced and how PostgreSQL may optimize it.
✓Write a recursive CTE to traverse hierarchical data.
✓Recognize PostgreSQL-specific data-modifying CTE behavior.

1. What Is a Common Table Expression?

A Common Table Expression (CTE) is a named result set defined at the beginning of a SQL statement using WITH.

Think of a CTE as giving a meaningful name to an intermediate query result. Instead of putting every calculation inside one large query, you can divide the work into understandable stages.

General syntax:

WITH cte_name AS (
SELECT ...
)
SELECT ...
FROM cte_name;

A CTE is scoped to the statement that uses it. It is not automatically saved as a permanent table.

Suppose you want to find products priced above the average price. You could write the average-price subquery directly inside WHERE, or name that calculation average_price in a CTE.

The CTE version makes the purpose of the intermediate result easier to understand.

This lesson uses PostgreSQL syntax. Some features, including data-modifying CTEs and particular materialization controls, vary across database engines.

2. Your First CTE

The WITH clause defines a CTE before the main query.

Execution conceptually follows this logic:
1. Define the named query result.
2. Reference it in the main SELECT.
3. Return the rows selected by the main query.

This is a logical explanation, not a guarantee that the database physically executes every CTE as a separate temporary table.

The example calculates an average product price and returns products priced above that average.

Find products priced above the average

sql

1
2
3
4
5
6
7
8
9
10
11
12
WITH average_price AS (
    SELECT AVG(price) AS avg_price
    FROM products
)
SELECT
    p.product_id,
    p.product_name,
    p.price
FROM products AS p
CROSS JOIN average_price AS a
WHERE p.price > a.avg_price
ORDER BY p.price DESC;

Output

Returns the products whose prices exceed the average price across the rows included in products. Exact rows depend on the table data.

The CTE calculates one aggregate row containing avg_price. CROSS JOIN makes that single result available to each product row. If products is empty, AVG returns NULL and no products qualify.

3. CTEs Versus Subqueries

A CTE and a subquery can often express the same logic. The main difference is how the query is organized.

Subquery approach:

SELECT product_id, product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);

CTE approach:

WITH average_price AS (
SELECT AVG(price) AS avg_price
FROM products
)
SELECT p.product_id, p.product_name, p.price
FROM products AS p
CROSS JOIN average_price AS a
WHERE p.price > a.avg_price;

Both approaches express the same comparison, assuming the same table and data.

Use a CTE when naming an intermediate result makes a query easier to follow, when several stages depend on one another, or when the result is referenced more than once.

Do not assume that replacing a subquery with a CTE automatically improves performance. Compare plans and measurements for performance-sensitive queries.

4. Chain Multiple CTEs

A WITH clause can define multiple CTEs separated by commas.

Later CTEs can reference earlier CTEs. This lets you express a query as a sequence of named transformations.

Example workflow:
1. Calculate each customer's order total.
2. Calculate the average customer total.
3. Return customers whose totals exceed that average.

Assume an orders table with order_id, customer_id, and total_amount. The example assumes total_amount represents the amount to aggregate for each order.

Calculate and compare customer totals

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
average_customer_total AS (
    SELECT AVG(total_spent) AS avg_spent
    FROM customer_totals
)
SELECT
    ct.customer_id,
    ct.total_spent
FROM customer_totals AS ct
CROSS JOIN average_customer_total AS act
WHERE ct.total_spent > act.avg_spent
ORDER BY ct.total_spent DESC;

Output

Returns customers whose aggregated order total is greater than the average total among customers represented in customer_totals. Exact rows depend on the orders data.

The second CTE uses the result of the first. Customers without orders are not included because the first CTE groups existing rows in orders.

5. Use CTEs with Filtering and Aggregation

CTEs are useful when an analysis has several distinct stages.

For example, an application might need:
- Only completed orders.
- The total amount spent by each customer on those orders.
- Only customers whose completed-order total exceeds a threshold.

Giving each stage a name makes it easier to verify that the query applies the intended filters before aggregation.

Be careful about where you place filters. Filtering rows before SUM can produce a different result from filtering groups after SUM.

Summarize completed orders

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH completed_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'completed'
),
customer_spending AS (
    SELECT
        customer_id,
        SUM(total_amount) AS completed_spending
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id, completed_spending
FROM customer_spending
WHERE completed_spending >= 1000
ORDER BY completed_spending DESC;

Output

Returns customers whose total amount across completed orders is at least 1000. Exact results depend on the schema, status values, and stored amounts.

The first CTE filters order rows. The second aggregates the filtered rows. The final query filters the resulting groups.

6. CTEs with Window Functions

A CTE can hold the result of a window function so the outer query can filter that result.

For example, ROW_NUMBER can rank orders within each customer. The outer query can then return each customer's most recent order.

Assume orders has order_id, customer_id, and order_date. The example uses order_id as a tie-breaker so that orders with identical timestamps still have a deterministic ordering.

Find each customer's latest order

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS row_num
    FROM orders
)
SELECT order_id, customer_id, order_date
FROM ranked_orders
WHERE row_num = 1
ORDER BY customer_id;

Output

Returns one latest order per customer who has at least one order. The highest order_id breaks ties when order_date is equal.

The CTE calculates row_num for each order. The outer query filters on row_num = 1. A CTE is useful here because the window-function result is computed in one query stage and filtered in the next.

7. Recursive CTEs: Work with Hierarchies

A recursive CTE can repeatedly follow relationships, such as an employee's manager or a category's parent category.

A recursive CTE normally has:
- An anchor term that selects the starting rows.
- A recursive term that references the CTE and finds the next rows.
- UNION ALL or UNION to combine the terms.

PostgreSQL evaluates the anchor term first and then repeatedly evaluates the recursive term using rows from the previous iteration until no more rows are produced.

Consider an employees table with employee_id, employee_name, and manager_id. The following query starts at a manager and finds all descendants beneath that manager.

The example assumes the hierarchy has no cycles. If cycles are possible, use an appropriate cycle-detection strategy to prevent revisiting nodes indefinitely.

Find all employees below a manager

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH RECURSIVE employee_tree AS (
    -- Anchor: begin with the chosen manager.
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE employee_id = 1
 
    UNION ALL
 
    -- Recursive term: find direct reports of rows already found.
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        et.depth + 1
    FROM employees AS e
    JOIN employee_tree AS et
        ON e.manager_id = et.employee_id
)
SELECT employee_id, employee_name, manager_id, depth
FROM employee_tree
ORDER BY depth, employee_id;

Output

Returns the employee with ID 1 at depth 0, followed by their direct reports at depth 1 and more distant descendants at increasing depths. Exact rows depend on the employees table.

The anchor selects employee 1. The recursive term finds employees whose manager_id matches an employee already in the tree. This example includes the starting manager in the result.

8. PostgreSQL Data-Modifying CTEs

PostgreSQL allows INSERT, UPDATE, and DELETE statements inside a WITH clause.

This can be useful when a single statement must modify data and return information from the modification. For example, a DELETE can return deleted rows using RETURNING, and the outer query can report them.

Important rules:
- A data-modifying CTE executes as part of the containing statement.
- Use RETURNING to expose rows modified by the statement to the rest of the query.
- Data-modifying CTEs use the same statement snapshot.
- Do not attempt to modify the same row more than once in one statement through multiple data-modifying CTEs; the outcome can be unpredictable.

The following example deletes expired sessions and returns the number of deleted rows. Run it only when deleting expired sessions is appropriate for your data.

Delete expired sessions and count them

sql

1
2
3
4
5
6
7
WITH deleted_sessions AS (
    DELETE FROM sessions
    WHERE expires_at < CURRENT_TIMESTAMP
    RETURNING session_id
)
SELECT COUNT(*) AS deleted_count
FROM deleted_sessions;

Output

Returns one row containing deleted_count, the number of expired sessions removed by the statement. The value depends on the data at execution time.

The DELETE operation exposes deleted session IDs through RETURNING. The outer SELECT counts those rows. The example assumes sessions.session_id exists and expires_at contains the expiration timestamp.

9. CTE Materialization and Performance

A CTE is a query-organization feature, not a guarantee that its result will be stored in a physical temporary table.

In PostgreSQL:
- Eligible non-recursive, side-effect-free CTEs referenced once can often be folded into the surrounding query.
- A CTE referenced multiple times may be materialized by default in relevant cases.
- MATERIALIZED can request separate calculation of a CTE result.
- NOT MATERIALIZED can allow eligible CTEs to be folded into the parent query, potentially enabling predicate pushdown but also repeating computation.

These options should not be used blindly. Materialization can avoid repeated expensive work, but it can also prevent optimizations that would otherwise reduce the number of rows processed.

For performance-sensitive queries:
1. Start with the clearest correct query.
2. Examine EXPLAIN output.
3. Use EXPLAIN (ANALYZE, BUFFERS) when you can safely execute the query and measure actual behavior.
4. Compare plans and timings with representative data.

EXPLAIN ANALYZE actually runs the statement. Take particular care with statements that modify data.

Inspect a query plan

sql

1
2
3
4
5
6
7
8
9
EXPLAIN
WITH expensive_products AS (
    SELECT product_id, product_name, price
    FROM products
    WHERE price >= 1000
)
SELECT product_id, product_name, price
FROM expensive_products
WHERE price < 5000;

Output

PostgreSQL returns a query plan describing how it intends to execute the query. The specific plan depends on table size, statistics, indexes, and database settings.

EXPLAIN displays the planned operations without executing the SELECT itself. Use EXPLAIN ANALYZE when actual execution measurements are required and it is safe to run the statement.

10. Common Mistakes

Mistake 1: Forgetting that a CTE exists only for its statement.
Correction: Define it again in another statement or use a view or table when a reusable database object is needed.

Mistake 2: Placing a comma incorrectly between CTE definitions.
Correction: Separate multiple CTEs with commas and put the final statement after the complete WITH clause.

Mistake 3: Assuming a CTE automatically improves performance.
Correction: Compare the query plans and measurements.

Mistake 4: Referencing a later CTE from an earlier CTE.
Correction: Define dependent CTEs in an order that allows each one to reference previously defined CTEs, except for supported recursive patterns.

Mistake 5: Writing a recursive CTE without considering cycles.
Correction: Ensure the hierarchy is valid or add a cycle-detection strategy.

Mistake 6: Using a window-function alias in the same SELECT's WHERE clause.
Correction: Put the window function in a CTE or subquery, then filter in the outer query.

Mistake 7: Using data-modifying CTEs without understanding their visibility rules.
Correction: Use RETURNING to pass modified rows to the main query and avoid modifying the same row more than once in one statement.

11. Practice Exercises

Exercise 1:
Write a CTE that selects products priced above 500.

Exercise 2:
Write two chained CTEs. The first calculates total spending per customer, and the second calculates average customer spending.

Exercise 3:
Use a CTE and ROW_NUMBER to find the latest order for each customer.

Exercise 4:
Explain why replacing a nested subquery with a CTE does not necessarily improve performance.

Exercise 5:
Write a recursive CTE that begins with a category ID and returns its descendants.

Exercise 6:
Write a PostgreSQL data-modifying CTE that deletes expired sessions and counts the rows deleted.

Exercise 7:
Explain the difference between a CTE, a view, and a temporary table.

Practice using a PostgreSQL database with sample tables. The examples in this lesson assume table and column names that may need to be adapted to your schema.

Practice solution: products above a threshold

sql

1
2
3
4
5
6
7
8
WITH qualifying_products AS (
    SELECT product_id, product_name, price
    FROM products
    WHERE price > 500
)
SELECT product_id, product_name, price
FROM qualifying_products
ORDER BY price DESC, product_id;

Output

Returns all products with a price greater than 500, ordered by price from highest to lowest and then by product ID. Exact rows depend on the products table.

The CTE names the filtered product set. The outer query chooses the output order.

12. Knowledge Check

Check your understanding of CTE structure, recursion, and execution behavior.

13. Interview Questions

Q1. What is a CTE?
Answer: A named query result defined using WITH and available to the statement that follows.

Q2. What is the difference between a CTE and a subquery?
Answer: Both can express similar logic. A CTE gives an intermediate query result a name, which can make multi-stage SQL easier to read.

Q3. Can a CTE reference another CTE?
Answer: Yes. A CTE can reference earlier CTEs in the same WITH clause.

Q4. What is a recursive CTE?
Answer: A CTE that repeatedly uses previously generated rows to find more rows, often for hierarchies.

Q5. What is the anchor term?
Answer: The non-recursive part that produces the initial rows of a recursive CTE.

Q6. Does a CTE create a permanent table?
Answer: No. An ordinary CTE exists only for its statement.

Q7. What are MATERIALIZED and NOT MATERIALIZED in PostgreSQL?
Answer: They influence whether eligible CTE results are calculated separately or can be folded into the surrounding query.

Q8. How do you investigate CTE performance?
Answer: Inspect EXPLAIN plans and, when appropriate, use EXPLAIN ANALYZE with representative data.

Q9. What is a data-modifying CTE in PostgreSQL?
Answer: A WITH query containing INSERT, UPDATE, or DELETE whose results can be exposed with RETURNING.

Q10. What is one risk of recursive CTEs?
Answer: A cyclic relationship can cause repeated traversal unless the query or data model handles cycles appropriately.

14. Lesson Summary

Common Table Expressions make complex SQL easier to understand by separating a statement into named stages.

Remember:
- Use WITH to define a CTE.
- Use commas to chain dependent CTEs.
- Use CTEs with aggregates, joins, and window functions.
- Use WITH RECURSIVE to traverse hierarchies.
- In PostgreSQL, data-modifying CTEs can return affected rows with RETURNING.
- A CTE is not a permanent table or automatically a performance optimization.
- Use EXPLAIN to inspect plans and EXPLAIN ANALYZE when actual execution measurements are appropriate.
- Consider recursion termination, cycles, NULL behavior, and data-modifying statement visibility.

The goal is to make SQL easier to reason about without losing correctness or ignoring the database engine's behavior.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming a CTE is a permanent table

Why it happens: The named result looks similar to a table reference.

Incorrect Approach

sql

WITH high_value_products AS (
    SELECT product_id, price
    FROM products
    WHERE price > 1000
)
SELECT * FROM high_value_products;
 
-- A separate statement cannot reference this CTE by name.

Correct Solution

sql

-- Define and use the CTE in the same statement.
WITH high_value_products AS (
    SELECT product_id, price
    FROM products
    WHERE price > 1000
)
SELECT product_id, price
FROM high_value_products
ORDER BY price DESC;

Key takeaway: An ordinary CTE is scoped to the statement containing its WITH clause. Use a view or table if a persistent database object is needed.

#2 Assuming a CTE always improves query performance

Why it happens: Readability improvements are confused with execution improvements.

Incorrect Approach

sql

-- Adding a CTE does not by itself prove
-- that a query will execute faster.

Correct Solution

sql

EXPLAIN
WITH selected_products AS (
    SELECT product_id, price
    FROM products
    WHERE price > 1000
)
SELECT product_id, price
FROM selected_products;

Key takeaway: Use a query plan to investigate execution behavior. Compare actual measurements when performance is important and execution is safe.

#3 Writing recursive SQL without considering termination

Why it happens: The recursive join is written without checking whether the hierarchy contains cycles.

Incorrect Approach

sql

-- A recursive relationship containing a cycle
-- may revisit the same nodes repeatedly.

Correct Solution

sql

-- Validate hierarchy data and use an appropriate
-- cycle-detection strategy where cycles are possible.
-- PostgreSQL also provides recursive CTE cycle-detection
-- syntax in supported versions.

Key takeaway: Recursive queries should have a clear stopping condition or cycle-handling strategy. The correct solution depends on the data model.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Common Table Expressions (CTEs) Knowledge Check

Test your understanding with immediate feedback

Question 1 of 6

Which keyword introduces a standard CTE?

Question 2 of 6

What is the scope of an ordinary CTE?

Question 3 of 6

Can a later CTE reference an earlier CTE in the same WITH clause?

Question 4 of 6

What are the two main parts of a recursive CTE?

Question 5 of 6

Does using a CTE guarantee a faster query?

Question 6 of 6

What does PostgreSQL RETURNING do in a DELETE CTE?

Key takeaways

A CTE is a named result set defined with WITH and available to the statement that follows it.
A CTE improves query organization but does not automatically make a query faster.
Later CTEs in a WITH clause can reference earlier CTEs.
A CTE is not automatically a permanent table or a stored view.
PostgreSQL may inline eligible CTEs or materialize them, depending on the query and optimization rules.
Recursive CTEs use a non-recursive term and a recursive term connected with UNION ALL or UNION.
PostgreSQL supports data-modifying statements inside WITH, with important execution and visibility rules.
Use EXPLAIN (ANALYZE, BUFFERS) when you need to measure actual query performance.

Was this lesson helpful?

Found an error? Report an issue