SQL CTEs Explained With Examples

A SQL CTE is one of the most useful features for making queries easier to understand.

CTE stands for Common Table Expression.

If that sounds more complicated than it needs to, the simple version is this:

A CTE lets you write a query in steps.

Instead of putting all your logic into:

• one giant nested query
• one unreadable subquery stack
• or one long statement with too many layers of parentheses

you can define a named intermediate result first, then query it as if it were a small temporary table inside the same statement.

That is why CTEs matter so much.

They do not just help with advanced SQL. They help with readable SQL.

This guide explains SQL CTEs in a practical way, with examples for:

• basic syntax
• step-by-step reporting queries
• multiple CTEs
• recursive CTEs
• updates and deletes
• data cleanup patterns
• and when to use a CTE instead of a subquery

Why SQL CTEs matter

A lot of SQL starts simple.

You write a query like:

SELECT *
FROM orders
WHERE total_amount > 100;

That is fine.

But real queries get more complicated quickly.

You may need to:

• filter data
• join related tables
• calculate totals
• rank rows
• remove duplicates
• reuse logic
• and then apply more filtering on the transformed result

At that point, SQL often becomes harder to read than it should be.

That is where CTEs help.

A CTE lets you say:

• first, calculate this intermediate result
• then, use that result in the next step
• then, continue from there

That makes the query much easier to reason about.

The most important rule

Before anything else, remember this:

A CTE is mainly a query-structure tool. Its biggest benefit is readability, clarity, and step-by-step logic.

That matters because many people first ask:

• are CTEs faster?

Sometimes performance is fine. Sometimes it is not the main benefit. The primary reason to use a CTE is usually:

• cleaner query design
• easier maintenance
• better debugging
• clearer reporting logic
• more understandable transformations

So when deciding whether to use a CTE, the first question is usually not:

• will this be faster?

It is:

• will this make the query clearer and easier to work with?

That is the right starting point.

What a CTE is

A CTE is a named result set created with the WITH clause.

Basic pattern:

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

This means:

• define a temporary result called cte_name
• then query from it in the main statement

That temporary result exists only for that one query. It is not a permanent table. It is not stored like a regular table in the schema.

It is just a clean named step inside the query.

Basic SQL CTE example

Suppose you want to find all high-value orders.

Without a CTE:

SELECT *
FROM (
    SELECT *
    FROM orders
    WHERE total_amount > 1000
) AS high_value_orders;

This works, but it is not especially readable.

With a CTE:

WITH high_value_orders AS (
    SELECT *
    FROM orders
    WHERE total_amount > 1000
)
SELECT *
FROM high_value_orders;

This is much easier to read because:

• the intermediate logic has a clear name
• the main query reads more naturally
• the query structure looks intentional

That is the core CTE advantage.

Why CTEs feel easier to read than nested subqueries

A nested subquery often forces the reader to:

• parse parentheses
• hold intermediate logic mentally
• understand inner queries before outer ones
• and deal with anonymous derived tables that may not have meaningful names

A CTE improves this by:

• naming the step
• separating the logic
• making the top-level query easier to scan
• and letting you think in stages

For example, this:

WITH monthly_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM monthly_revenue
WHERE total_revenue > 5000;

is much more readable than putting the grouped logic inline as a subquery and then filtering around it.

That readability advantage becomes more important as queries grow.

CTE syntax explained simply

The core syntax is:

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

You can think of it like:

• WITH starts the temporary named query block
• cte_name is the name you choose
• AS (...) contains the query that defines that result
• the final query uses the CTE like a regular source

The result set inside the CTE can contain:

• filtered rows
• joined rows
• grouped rows
• ranked rows
• deduplicated rows
• or any other query result you want to build first

CTE example with filtering and grouping

Suppose you want to find customers whose total revenue is greater than 5000.

You can write:

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_revenue
WHERE total_revenue > 5000;

This query works in two clear steps:

Step 1

Create customer_revenue:

• group orders by customer
• calculate total revenue per customer

Step 2

Query that result:

• keep only customers with revenue above 5000

This is exactly the kind of logic CTEs are great for.

CTEs for step-by-step reporting

One of the best uses of CTEs is reporting logic that happens in stages.

Imagine you want to:

1. calculate order totals by customer
2. classify customers into revenue bands
3. count how many customers fall into each band

You could write:

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        total_revenue,
        CASE
            WHEN total_revenue >= 10000 THEN 'VIP'
            WHEN total_revenue >= 5000 THEN 'High Value'
            ELSE 'Standard'
        END AS revenue_segment
    FROM customer_revenue
)
SELECT
    revenue_segment,
    COUNT(*) AS customer_count
FROM customer_segments
GROUP BY revenue_segment
ORDER BY customer_count DESC;

This is a great example because it shows how CTEs let you build logic in layers:

• aggregate first
• classify second
• summarize third

Without CTEs, this kind of reporting often becomes much harder to read.

Multiple CTEs in one query

You are not limited to one CTE. You can define several in sequence.

Pattern:

WITH first_cte AS (
    ...
),
second_cte AS (
    ...
),
third_cte AS (
    ...
)
SELECT ...
FROM third_cte;

This makes CTEs very powerful for multi-step logic.

Each CTE can:

• build on base tables
• or build on earlier CTEs

That means you can model a whole pipeline inside one query.

This is one of the reasons analysts and developers both use them so much.

Example: top products by category

Suppose you want to:

1. total product sales
2. rank products within each category
3. return the top 3 per category

Using CTEs:

WITH product_sales AS (
    SELECT
        p.category_id,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM order_items oi
    JOIN products p
        ON oi.product_id = p.product_id
    GROUP BY
        p.category_id,
        p.product_id,
        p.product_name
),
ranked_products AS (
    SELECT
        category_id,
        product_id,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM product_sales
)
SELECT
    category_id,
    product_id,
    product_name,
    total_sales
FROM ranked_products
WHERE sales_rank <= 3
ORDER BY category_id, total_sales DESC;

This is a very realistic pattern:

• summarize
• rank
• filter top results

CTEs make each phase explicit.

CTEs versus subqueries

A very common question is:

• should I use a CTE or a subquery?

The honest answer is:

• both are useful
• neither replaces the other completely

A subquery is often fine when:

• the logic is small
• it is used once
• the query remains easy to read

A CTE is often better when:

• the logic deserves a name
• the query has multiple steps
• the same intermediate result is conceptually important
• readability would suffer with nested subqueries
• recursive logic is needed

So the practical rule is simple:

Use a CTE when it makes the query easier to understand.

That is usually the best reason.

A simple comparison

Subquery style

SELECT *
FROM (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
) AS revenue_summary
WHERE total_revenue > 5000;

CTE style

WITH revenue_summary AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM revenue_summary
WHERE total_revenue > 5000;

Both are valid. The CTE version is often easier to read and maintain.

CTEs for duplicate cleanup

This is one of the most practical use cases.

Suppose you want to find duplicate user emails and keep only the earliest row.

A CTE with ROW_NUMBER() works very well:

WITH ranked_users AS (
    SELECT
        id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at ASC
        ) AS rn
    FROM users
)
SELECT *
FROM ranked_users
WHERE rn > 1;

This:

• partitions rows by email
• ranks them oldest first
• marks duplicates as rn > 1

Then to delete duplicates:

WITH ranked_users AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at ASC
        ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id
    FROM ranked_users
    WHERE rn > 1
);

This is one of the best examples of a CTE making a complex operation safer and clearer.

CTEs with UPDATE

CTEs are not only for SELECT queries. They are also very useful in updates.

Example: Suppose you want to classify customers by total spend and update a segment column.

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
UPDATE customers
SET segment =
    CASE
        WHEN customer_revenue.total_revenue >= 10000 THEN 'VIP'
        WHEN customer_revenue.total_revenue >= 5000 THEN 'High Value'
        ELSE 'Standard'
    END
FROM customer_revenue
WHERE customers.customer_id = customer_revenue.customer_id;

This is useful because:

• the revenue logic is separated clearly
• the update uses a clean intermediate result
• the business rule stays readable

Without the CTE, this often becomes harder to follow.

CTEs with DELETE

CTEs are also very useful when deleting rows based on ranking, filtering, or other derived logic.

Example: Delete orders older than 3 years from inactive customers.

WITH inactive_customers AS (
    SELECT customer_id
    FROM customers
    WHERE is_active = false
)
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM inactive_customers
)
AND order_date < CURRENT_DATE - INTERVAL '3 years';

This is much easier to read than putting everything inline.

It also makes the deletion logic easier to verify before running it.

CTEs with INSERT

You can also use a CTE to prepare data before inserting it somewhere else.

Example: Insert high-value customers into a reporting table.

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
INSERT INTO high_value_customers (customer_id, total_revenue)
SELECT
    customer_id,
    total_revenue
FROM customer_revenue
WHERE total_revenue > 5000;

This is useful when:

• creating summary tables
• preparing analytics datasets
• populating reporting layers
• or moving filtered data into another destination

Recursive CTEs

Recursive CTEs are one of the most powerful CTE features.

A recursive CTE is used when data has a hierarchical or repeated parent-child structure, such as:

• employees and managers
• categories and subcategories
• folders and subfolders
• comments and replies
• organizational trees
• bill of materials structures

A recursive CTE lets the query repeatedly follow the hierarchy.

This is where CTEs go from useful to very powerful.

Recursive CTE structure

A recursive CTE usually has two parts:

• the anchor query
• the recursive query

Pattern:

WITH RECURSIVE cte_name AS (
    -- anchor query
    SELECT ...

    UNION ALL

    -- recursive query
    SELECT ...
    FROM ...
    JOIN cte_name ON ...
)
SELECT *
FROM cte_name;

The anchor query starts the result. The recursive query keeps adding more rows based on earlier results.

That continues until no new matching rows are found.

Example: employee hierarchy

Suppose you have an employees table like this:

• employee_id
• employee_name
• manager_id

And you want to list all employees under one manager.

WITH RECURSIVE employee_hierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1 AS level
    FROM employees e
    JOIN employee_hierarchy eh
        ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_id;

This does:

• start with employee 1
• find direct reports
• then direct reports of direct reports
• then keep going through the hierarchy

That is the classic recursive CTE use case.

Example: category tree

Suppose categories can have parent categories.

You might want to retrieve the full tree below a starting category.

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS level
    FROM categories
    WHERE category_id = 10

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.level + 1 AS level
    FROM categories c
    JOIN category_tree ct
        ON c.parent_category_id = ct.category_id
)
SELECT *
FROM category_tree
ORDER BY level, category_id;

This is useful in:

• ecommerce category navigation
• CMS hierarchies
• organizational structures
• folder systems

When recursive CTEs are useful

Recursive CTEs are useful when:

• data references itself
• parent-child relationships continue for multiple levels
• you want to flatten a hierarchy
• you need ancestry or descendants
• normal joins are not enough because the depth is not fixed

They are especially valuable because they let you query unknown-depth hierarchies without hardcoding:

• join level 1
• join level 2
• join level 3
• and so on

That is one of the most advanced and powerful features in standard SQL querying.

CTEs for readability in long queries

A lot of the time, you do not use a CTE because you need advanced logic. You use it because the query is easier to read in stages.

For example, imagine you need to:

• filter paid orders from the last 90 days
• join customers
• group totals by customer
• classify them into tiers
• then show the top customers

You could write one huge query. But a step-based CTE version is often much easier to maintain.

Example:

WITH recent_paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'Paid'
      AND order_date >= CURRENT_DATE - INTERVAL '90 days'
),
customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM recent_paid_orders
    GROUP BY customer_id
),
customer_tiers AS (
    SELECT
        customer_id,
        total_revenue,
        CASE
            WHEN total_revenue >= 10000 THEN 'VIP'
            WHEN total_revenue >= 5000 THEN 'High Value'
            ELSE 'Standard'
        END AS customer_tier
    FROM customer_revenue
)
SELECT *
FROM customer_tiers
ORDER BY total_revenue DESC;

This is not just nicer to read. It is easier to debug and modify later.

CTEs for debugging query logic

Another great benefit of CTEs is debugging.

When a large query is wrong, a nested version can be painful to troubleshoot. A CTE version lets you inspect each stage more easily.

For example, if the final result looks wrong, you can:

• run only the first CTE logic
• inspect intermediate rows
• verify the grouped numbers
• then verify the ranking or CASE logic
• and isolate exactly where the problem starts

That is a huge practical advantage.

In other words: CTEs do not only help the finished query. They help the debugging process too.

CTE naming best practices

A CTE is easier to understand when it has a useful name.

Good names usually describe what the CTE contains.

Examples:

• high_value_orders
• customer_revenue
• ranked_products
• recent_signups
• duplicate_emails
• employee_hierarchy

Bad names are vague and do not help the reader.

Examples:

• temp1
• cte_data
• x
• query_result

A CTE name should tell you what the intermediate step means.

That is part of why CTEs improve readability.

Common mistakes with SQL CTEs

There are a few CTE mistakes that show up often.

1. Using a CTE when the query would be clearer without it

CTEs are useful, but not every small query needs one.

This is overkill:

WITH active_users AS (
    SELECT *
    FROM users
    WHERE is_active = true
)
SELECT *
FROM active_users;

This is valid, but if the query is tiny and unlikely to grow, the CTE may not add much value.

A CTE is most useful when it improves structure, not just because it can exist.

2. Giving CTEs vague names

If the CTE name does not describe the step, readability drops fast.

3. Stacking too many unnecessary CTE layers

Too many thin layers can make a query harder, not easier.

The goal is:

• meaningful steps not
• maximum number of CTEs

4. Forgetting that CTEs are query-scoped

A CTE does not persist outside the statement. You cannot define it once and use it later in another separate query.

It exists only inside that one statement.

5. Assuming CTEs always improve performance

CTEs mainly improve readability and query structure. Performance still depends on:

• the engine
• the optimizer
• the execution plan
• the indexing
• and the actual SQL logic

That is why CTEs should be used for clarity first, then measured when performance matters.

CTEs versus views

A CTE and a view are not the same thing.

CTE

• exists only for one query
• great for temporary logic inside one statement
• useful when you do not need permanent reuse

View

• stored in the database schema
• reusable across many queries
• better when the same logic is needed repeatedly across many reports or applications

A simple practical rule is:

Use a CTE when:

• the logic belongs to one query

Use a view when:

• the logic should be reused across many queries

That distinction helps keep query design clean.

When to choose a CTE

A CTE is a strong choice when:

• the query has multiple logical steps
• you want cleaner structure than a nested subquery
• you want to name an intermediate result
• you need ranking or deduplication logic
• you need recursion
• you want the query to be easier to debug
• you are writing reporting or analytics SQL that benefits from stepwise logic

Those are the situations where CTEs shine.

FAQ

What is a CTE in SQL?

A CTE, or Common Table Expression, is a named temporary result set defined with the WITH clause that can be referenced inside the main query.

When should I use a CTE instead of a subquery?

Use a CTE when the query becomes easier to read in steps, when intermediate logic deserves a name, when multiple stages of transformation are involved, or when recursive logic is needed.

Do SQL CTEs improve performance?

Not automatically. CTEs mainly improve readability and query structure. Performance depends on the database engine, the execution plan, and the actual logic inside the CTE.

Can I use a CTE with UPDATE or DELETE?

Yes. Many SQL databases allow CTEs to be used with SELECT, INSERT, UPDATE, and DELETE statements, which makes them useful for cleanup, ranking, and controlled data changes.

Final thoughts

SQL CTEs are one of the best tools for making complex queries understandable.

They help because they let you:

• break a query into steps
• name intermediate logic
• simplify multi-stage transformations
• write cleaner reporting SQL
• handle ranking and deduplication more safely
• and work with recursive hierarchies when needed

That is why the best way to think about a CTE is simple:

It is not just a temporary result. It is a way of writing SQL that is easier for humans to understand.

And in real systems, that matters a lot.

Because SQL is not only about getting the right answer once. It is about writing queries you and your team can still read, debug, and trust later.