CTEs and Subqueries in SQL

A subquery is a query nested inside another query, and a CTE (Common Table Expression) is a named, temporary result set you define with WITH at the top of a query. Both let you break a hard question into smaller steps; CTEs are usually easier to read and reuse, while subqueries are handy for quick, one-off filters. This guide shows when to use each, with examples you can run on almost any SQL database.

Why analysts need CTEs and subqueries

Most real business questions take more than one step. "Which customers spent more than the average customer last quarter?" requires you to first calculate the average, then compare each customer to it. "What are the top three products in each region?" requires you to rank products inside each region, then keep only the first three. A single flat SELECT can't do that cleanly.

Subqueries and CTEs solve this by letting you build an intermediate result and then query it. If you are comfortable with GROUP BY and aggregations, you already have the building blocks; CTEs and subqueries are how you stack those blocks together.

For the examples below, imagine two simple tables:

• sales: sale_id, sale_date, customer_id, product_id, region, amount
• customers: customer_id, customer_name, segment

Subqueries: the three places they appear

A subquery is simply a SELECT wrapped in parentheses. Where you put it changes what it does.

1. In the WHERE clause (filtering)

This is the most common use. The inner query returns a value or a list, and the outer query filters against it.

-- Sales larger than the overall average sale
SELECT sale_id, customer_id, amount
FROM sales
WHERE amount > (SELECT AVG(amount) FROM sales);

-- Sales from customers in the Enterprise segment
SELECT sale_id, amount
FROM sales
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE segment = 'Enterprise'
);

2. In the FROM clause (derived tables)

Here the subquery acts like a temporary table. Most databases require you to give it an alias.

SELECT region, AVG(customer_total) AS avg_spend_per_customer
FROM (
    SELECT region, customer_id, SUM(amount) AS customer_total
    FROM sales
    GROUP BY region, customer_id
) AS t
GROUP BY region;

3. In the SELECT list (scalar subqueries)

A scalar subquery returns exactly one value per row. It is useful for adding a comparison column, such as each sale's share of the total.

SELECT sale_id,
       amount,
       amount * 100.0 / (SELECT SUM(amount) FROM sales) AS pct_of_total
FROM sales;

Correlated subqueries

A correlated subquery references a column from the outer query, so it is evaluated in relation to each outer row. The classic example: find sales that are above the average for their own region.

SELECT s.sale_id, s.region, s.amount
FROM sales AS s
WHERE s.amount > (
    SELECT AVG(s2.amount)
    FROM sales AS s2
    WHERE s2.region = s.region
);

Correlated subqueries are powerful but can be slow on large tables, and they are harder to read. That is where CTEs shine.

CTEs: naming your steps with WITH

A CTE lets you write the intermediate step first, give it a name, and then use that name like a table. Here is the derived-table example from above, rewritten as a CTE:

WITH customer_totals AS (
    SELECT region, customer_id, SUM(amount) AS customer_total
    FROM sales
    GROUP BY region, customer_id
)
SELECT region, AVG(customer_total) AS avg_spend_per_customer
FROM customer_totals
GROUP BY region;

The logic is identical, but the query now reads top to bottom, like a recipe. You can chain several CTEs by separating them with commas:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM sales
    WHERE sale_date >= '2026-07-01' AND sale_date < '2026-10-01'
    GROUP BY customer_id
),
overall AS (
    SELECT AVG(total_spent) AS avg_spent
    FROM customer_totals
)
SELECT c.customer_name, ct.total_spent
FROM customer_totals AS ct
JOIN customers AS c ON c.customer_id = ct.customer_id
CROSS JOIN overall AS o
WHERE ct.total_spent > o.avg_spent
ORDER BY ct.total_spent DESC;

This answers "which customers spent more than the average customer last quarter?" in three readable steps. Notice that the second CTE (overall) reads from the first one. That ability to build on previous steps is the main reason analysts love CTEs.

Top N per group: CTE + window function

One of the most common reporting requests is "top three products per region." You can't filter on a window function directly in WHERE, so you calculate the rank in a CTE and filter outside it. If window functions are new to you, see our guide to SQL window functions.

WITH product_sales AS (
    SELECT region, product_id, SUM(amount) AS revenue
    FROM sales
    GROUP BY region, product_id
),
ranked AS (
    SELECT region, product_id, revenue,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
    FROM product_sales
)
SELECT region, product_id, revenue
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

Recursive CTEs

A recursive CTE refers to itself, which makes it possible to walk hierarchies (employees and managers, category trees) or generate a series of dates. In PostgreSQL, MySQL 8+ and SQLite you write WITH RECURSIVE; in SQL Server you write just WITH. This PostgreSQL example generates every date in October 2026, which is handy for building a calendar or filling gaps in daily sales:

WITH RECURSIVE dates AS (
    SELECT DATE '2026-10-01' AS d
    UNION ALL
    SELECT d + 1
    FROM dates
    WHERE d < DATE '2026-10-31'
)
SELECT d FROM dates;

Always include a stopping condition (the WHERE in the recursive part). Without it, the query keeps going until the database hits its recursion limit or returns an error.

CTE vs subquery: which one should you use?

On performance: in many modern databases the optimizer treats a simple CTE and the equivalent subquery the same way, so the choice is mostly about readability. Behavior does vary by engine and version (for example, whether a CTE is "materialized" once or inlined), so if a query is slow, compare the execution plans rather than assuming one style is always faster.

Common mistakes to avoid

• Using = with a subquery that returns many rows. WHERE customer_id = (SELECT customer_id FROM ...) fails if the inner query returns more than one row. Use IN or EXISTS.
• NOT IN with NULLs. If the subquery list contains a NULL, NOT IN returns no rows. Prefer NOT EXISTS or filter out NULLs inside the subquery.
• Forgetting the alias on a derived table. PostgreSQL, MySQL and SQL Server all expect FROM (SELECT ...) AS t.
• Missing semicolon before WITH in SQL Server. If a previous statement in the same batch isn't terminated, SQL Server may complain. Ending the previous statement with ; fixes it.
• Using ORDER BY inside a CTE. Ordering inside a CTE generally doesn't guarantee the final output order. Put ORDER BY in the outer query.

Using CTEs in Power BI and other BI tools

CTEs are perfect for building clean views that a dashboard connects to. You can write the query as a database view and point Power BI at the view, or paste it as a native SQL statement when connecting. Keep in mind that Power BI may wrap native queries in its own query, which can limit query folding in Power Query. Our guide on connecting Power BI to SQL databases explains the trade-offs.

Next steps

1. Take one query you already use that has a nested subquery and rewrite it as a CTE.
2. Build a "top 3 per group" query on your own sales data using a CTE and ROW_NUMBER().
3. Try a recursive CTE that generates a date table, then join your sales to it to find days with zero sales.
4. Practice more with our SQL practice projects.

Watch: video lessons

▶

▶

Frequently asked questions