SQL subqueries and CTEs: breaking complex queries into understandable steps

A query can use the result of another query

So far, our SQL queries have mainly worked directly with tables. Consider the same orders and customers tables:

Sometimes one SQL operation is not enough to express the question clearly. For example, “which orders are worth more than the average order value?” Before we can identify those orders, we first need to know the average order value, and that calculation is itself a query: SELECT AVG(quantity * unit_price) FROM orders;. A subquery allows us to place one query inside another query and use its result: solve one part of the problem, use that result, then solve the larger problem.

Scalar subqueries return one value

A scalar subquery is a subquery used where SQL expects a single value. Suppose we want orders whose value is greater than the average order value. The order values are 1500, 3200, 3600, 8500, 750, 3600, and their average is 3525. Now use that value inside another query:

SELECT
    order_id,
    customer_id,
    quantity * unit_price AS order_amount
FROM orders
WHERE quantity * unit_price > (
    SELECT AVG(quantity * unit_price)
    FROM orders
)
ORDER BY order_id;

The inner query produces one value, 3525, so the outer query behaves conceptually like WHERE quantity * unit_price > 3525. That is why this is called a scalar subquery.

Scalar subqueries can appear in SELECT

A scalar subquery can also produce a value in the SELECT list:

SELECT
    order_id,
    quantity * unit_price AS order_amount,
    (
        SELECT AVG(quantity * unit_price)
        FROM orders
    ) AS overall_average
FROM orders
ORDER BY order_id;

The same overall average appears beside every order because the subquery does not depend on the current order.

Gotcha: a scalar subquery must produce one value

Correlated subqueries use the current outer row

The previous scalar subquery calculated one average for the entire orders table. Now consider a different requirement: “return orders whose value is greater than that customer’s own average order value.” For Asha, orders 1500 and 750 average 1125; for Ben, 3200 and 3600 average 3400. Each order must be compared against a different average depending on its customer. We can use a correlated subquery:

SELECT
    o.order_id,
    o.customer_id,
    o.quantity * o.unit_price AS order_amount
FROM orders AS o
WHERE o.quantity * o.unit_price > (
    SELECT AVG(o2.quantity * o2.unit_price)
    FROM orders AS o2
    WHERE o2.customer_id = o.customer_id
)
ORDER BY o.order_id;

The important condition is WHERE o2.customer_id = o.customer_id. The inner query refers to o.customer_id from the current row in the outer query, so the subquery changes depending on which order SQL is evaluating.

How the correlation works

For order 1001 (o.customer_id = 1), the inner query conceptually becomes SELECT AVG(...) FROM orders WHERE customer_id = 1;. Asha’s average is 1125, and order 1001 is worth 1500:

Order 1001 (Asha):  1500 > 1125  → TRUE
Order 1005 (Asha):   750 > 1125  → FALSE
Order 1006 (Ben):   3600 > 3400  → TRUE
Order 1002 (Ben):   3200 > 3400  → FALSE

The missing correlation that answers the wrong question

Suppose the requirement is find orders worth more than the customer’s average order value. Someone writes the query below. It runs successfully, but something important is missing: o2.customer_id = o.customer_id.

SELECT
    o.order_id,
    o.customer_id,
    o.quantity * o.unit_price AS order_amount
FROM orders AS o
WHERE o.quantity * o.unit_price > (
    SELECT AVG(o2.quantity * o2.unit_price)
    FROM orders AS o2
)
ORDER BY o.order_id;

The fix: connect the inner query to the outer row

Add the correlation. The condition o2.customer_id = o.customer_id is what changes the meaning from the overall average to this customer’s average:

SELECT
    o.order_id,
    o.customer_id,
    o.quantity * o.unit_price AS order_amount
FROM orders AS o
WHERE o.quantity * o.unit_price > (
    SELECT AVG(o2.quantity * o2.unit_price)
    FROM orders AS o2
    WHERE o2.customer_id = o.customer_id
)
ORDER BY o.order_id;

Use a query result like a table

So far, our subqueries have appeared inside WHERE and SELECT. A subquery can also appear inside FROM, and when it does, its result acts like a temporary table for the surrounding query. This is commonly called a derived table. Suppose we want customers whose total order value exceeds 5,000. First, calculate one row per customer, then query that intermediate result as if it were a table:

SELECT
    customer_id,
    total_order_value
FROM (
    SELECT
        customer_id,
        SUM(quantity * unit_price) AS total_order_value
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_order_value > 5000
ORDER BY customer_id;

The inner query creates customer_totals; the outer query then filters that intermediate result. Derived tables are useful when a problem naturally has stages: stage 1 calculates one total per customer, and stage 2 filters those customer totals. This can be easier to understand than trying to express every transformation in one flat query, and it is also useful when the result of one query needs to be filtered, joined, aggregated again, ranked, or transformed further.

Common table expressions with WITH

Derived tables work well, but deeply nested queries can become difficult to read. SQL provides another way to name an intermediate query result: WITH, which creates a Common Table Expression, usually called a CTE. The previous derived-table query can be written as:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(quantity * unit_price) AS total_order_value
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_order_value
FROM customer_totals
WHERE total_order_value > 5000
ORDER BY customer_id;

The main query can then refer to customer_totals almost as though it were a table.

CTEs help name the steps of a problem

Consider “among paid orders, find customers whose total paid order value exceeds 3,000.” A CTE lets us name stage 1 (calculate paid totals), and the main query handles stage 2 (keep totals above 3,000):

WITH paid_customer_totals AS (
    SELECT
        customer_id,
        SUM(quantity * unit_price) AS paid_order_value
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT
    customer_id,
    paid_order_value
FROM paid_customer_totals
WHERE paid_order_value > 3000
ORDER BY customer_id;

The query reads almost like the problem: create paid customer totals, then return totals above 3000. That readability is one of the main reasons CTEs are useful.

CTEs can be joined to other tables

A CTE can also become an input to a join. To show customer names alongside their totals:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(quantity * unit_price) AS total_order_value
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    ct.total_order_value
FROM customer_totals AS ct
JOIN customers AS c
    ON c.customer_id = ct.customer_id
ORDER BY c.customer_name;

The CTE prepares the order-level data; the main query adds customer information. This helps separate “how should orders be summarised?” from “how should the result be presented?”

Multiple CTEs can represent multiple steps

A WITH clause can define more than one CTE. If the problem has two logical stages, calculate customer totals then keep high-value customers, we could chain them:

WITH customer_totals AS (
    SELECT customer_id, SUM(quantity * unit_price) AS total_order_value
    FROM orders
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id, total_order_value
    FROM customer_totals
    WHERE total_order_value > 5000
)
SELECT c.customer_name, h.total_order_value
FROM high_value_customers AS h
JOIN customers AS c ON c.customer_id = h.customer_id
ORDER BY c.customer_name;

This does not mean every query should be split into many CTEs. Use them when the names make the logic easier to follow; too many tiny CTEs can make a simple query harder rather than easier.

Derived table or CTE, and a word on performance

These two forms can often express the same logic; the choice is often about readability. A derived table works well when the intermediate result is short, used once, and easy to understand inline. A CTE is often clearer when the intermediate query is substantial, the logic has named stages, several intermediate results are involved, or the same intermediate result needs to be referenced again within the statement, where supported.

When to consider a subquery or CTE

Consider a subquery or CTE when one calculation depends on the result of another, you need to compare a value against an overall aggregate, you need to compare a row against an aggregate for its own group, a query naturally breaks into several logical stages, you want to aggregate data and then perform more work on that result, a nested query is becoming difficult to read, or an intermediate result deserves a meaningful name. Watch for these common mistakes:

Ask what the dependency actually is

One more prediction

Consider these orders. Requirement: return orders whose value is greater than the average order value for that same customer. Which query is correct?

-- Option A
WHERE o.order_amount > (
    SELECT AVG(o2.order_amount) FROM orders AS o2
)

-- Option B
WHERE o.order_amount > (
    SELECT AVG(o2.order_amount) FROM orders AS o2
    WHERE o2.customer_id = o.customer_id
)

-- Option C
WHERE o.customer_id = (
    SELECT AVG(o2.order_amount) FROM orders AS o2
)

-- Option D
WHERE o.order_amount > AVG(o.order_amount)

Summary

A subquery lets one query use the result of another query. Use a scalar subquery when one value is required; a correlated subquery when that calculation depends on the current outer row; a derived table when you need a multi-row intermediate result; and a CTE when naming that intermediate result makes the query easier to follow.

Next, we will move into working with real-world date and timestamp data, including filtering periods, date arithmetic, extraction, and boundary-safe time ranges.