SQL subqueries and CTEs: breaking complex SQL queries into understandable steps

In this lesson, we will learn how to use subqueries to solve these problems and how common table expressions (CTEs) help organise larger queries into smaller, readable steps.

We will also explore common mistakes, including subqueries that return unexpected values, incorrect comparisons between aggregates, and CTEs that produce different results than intended.

Sample data

We will continue using the customers and orders tables from P07.

We have already used queries inside other queries in earlier lessons. For example:

• In P07, we used a derived table to summarise payments before joining them to orders.
• In P08, we used IN and EXISTS subqueries to check whether matching rows exist.
• In P09, we combined order records in a derived table before calculating totals.

All these examples use the same idea: a query whose result is used by another query. This is called a subquery. A subquery can return a single value, a list of values, or an entire table. What it needs to return depends on where we use it.

Another important distinction is whether a subquery depends on the outer query.

• Uncorrelated subquery: Does not reference columns from the outer query. Its result is independent of the current outer row.
• Correlated subquery: References columns from the outer query, so its result can change depending on which outer row is being processed.

We explored correlated subqueries with EXISTS in P08. Here, we will also use them to calculate values for individual rows.

Finance wants to find orders whose amounts are greater than the average order amount.

First, let’s calculate the average across all orders.

SELECT ROUND(AVG(amount), 2) AS avg_amount
FROM orders;

The average order amount is approximately 3,478.57. We now need to compare each order’s amount with this average. We cannot simply write WHERE amount > AVG(amount) because aggregate functions such as AVG() cannot be used directly in WHERE at the same query level. Instead, we can calculate the average in a subquery and use its result in the comparison.

SELECT order_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
)
ORDER BY order_id;

The inner query calculates the average order amount, and the outer query keeps orders whose amounts are greater than that value. A subquery used as a single value is called a scalar subquery. Scalar subqueries can be used wherever SQL expects one value, such as a comparison, an expression in SELECT, or a condition inside CASE.

Finance changes the requirement: Find paid orders whose amounts are greater than the average paid order amount.

Someone adds status = 'paid' to the outer query:

SELECT order_id, amount
FROM orders
WHERE status = 'paid'
  AND amount > (
      SELECT AVG(amount)
      FROM orders
  )
ORDER BY order_id;

The fix: Filter both queries

To compare paid orders with the average paid order amount, we must apply the paid-order condition inside the subquery as well.

SELECT order_id, amount
FROM orders
WHERE status = 'paid'
  AND amount > (
      SELECT AVG(amount)
      FROM orders
      WHERE status = 'paid'
  )
ORDER BY order_id;

Now the two conditions serve different purposes:

• The outer WHERE status = 'paid' decides which orders can appear in the result.
• The inner WHERE status = 'paid' decides which orders contribute to the average.

The query now meets the requirement.

A scalar subquery does not always need an aggregate function. It can also retrieve a value from a particular row, such as an order amount identified by an order ID or date. However, because a scalar subquery represents a single value, we need to understand what happens when it returns no rows or more than one row.

Consider a new requirement from Finance. The shop wants to create an index that compares every order amount with the first order of a May promotion. The first promotional order should have an index value of 100. For example, if the reference order is worth 3,200, an order worth 6,400 would receive an index value of 200.

The formula is: index_value = 100 × order_amount / reference_amount

An analyst initially assumes that the promotion started on May 1 and selects the reference order by date.

SELECT
    order_id,
    amount,
    100.0 * amount / (
        SELECT amount
        FROM orders
        WHERE order_date = '2026-05-01'
    ) AS index_value
FROM orders
ORDER BY order_id;

Finance confirms that the promotion actually started on May 3. The analyst updates the date:

SELECT
    order_id,
    amount,
    100.0 * amount / (
        SELECT amount
        FROM orders
        WHERE order_date = '2026-05-03'
    ) AS index_value
FROM orders
ORDER BY order_id;

The fix: Select a unique reference row

Finance confirms that order 1002 is the first promotional order.

Instead of selecting the reference order by date, use its unique order_id.

SELECT
    order_id,
    amount,
    ROUND(
        100.0 * amount / (
            SELECT amount
            FROM orders
            WHERE order_id = 1002
        ),
        1
    ) AS index_value
FROM orders
ORDER BY order_id;

Now the reference value is always 3,200, provided order 1002 exists. The query is reliable because order_id is defined to uniquely identify an order, not merely because the current sample happens to contain one matching row. If the reference should represent several orders, use an aggregate instead. For example, SUM(amount) could calculate the total amount for a day, while MIN(amount) could find its smallest order amount.

So far, our scalar subqueries have calculated a value that is the same for every row of the outer query. But what if the value should depend on the current row?

Consider the requirement: Show every order along with the average order amount for that order’s customer.

Asha’s orders should be compared with Asha’s average, Ben’s orders with Ben’s average, and so on. This requires a correlated scalar subquery.

SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    (
        SELECT AVG(other.amount)
        FROM orders AS other
        WHERE other.customer_id = o.customer_id
    ) AS customer_avg
FROM orders AS o
ORDER BY o.order_id;

How does the correlation work?

Notice that we read orders twice, using two different aliases:

• o represents the order in the outer query.
• other represents orders being used to calculate the customer’s average.

The condition connects them: WHERE other.customer_id = o.customer_id

• For order 1001, the outer customer ID is 1, so the subquery calculates the average of Asha’s orders: (1,500 + 750) / 2 = 1,125
• For order 1002, the customer ID is 2, so the subquery calculates Ben’s average: (3,200 + 3,600) / 2 = 3,400

The same logic applies to every order. Guest order 1007 has a NULL customer ID. Because an ordinary equality comparison cannot match NULL, the subquery finds no matching orders. Its average is therefore NULL.

Using the correlated average as a filter

We can also use the subquery in WHERE to find orders whose amounts are greater than their own customer’s average order amount.

SELECT
    o.order_id,
    o.customer_id,
    o.amount
FROM orders AS o
WHERE o.amount > (
    SELECT AVG(other.amount)
    FROM orders AS other
    WHERE other.customer_id = o.customer_id
)
ORDER BY o.order_id;

Order 1001 exceeds Asha’s average of 1,125, while order 1006 exceeds Ben’s average of 3,400. Carla and Dev have only one order each, so their order amounts equal their averages and do not qualify. Without the correlation condition, every order would instead be compared with the overall average of 3,478.57, which answers a different question.

Watch out: Reusing the same alias

A more subtle mistake happens when we give the inner and outer tables the same alias.

Consider:

SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    (
        SELECT AVG(o.amount)
        FROM orders AS o
        WHERE o.customer_id = o.customer_id
    ) AS customer_avg
FROM orders AS o
ORDER BY o.order_id;

Every row now has the same average. Why? Inside the subquery, o refers to the inner orders table because its alias hides the outer alias. Therefore, this condition: WHERE o.customer_id = o.customer_id compares each inner row’s customer ID with itself, rather than comparing the inner row with the outer order.

For non-NULL customer IDs, the condition is TRUE. For guest order 1007, NULL = NULL evaluates to UNKNOWN, so that row is excluded. The subquery therefore averages the six orders with non-NULL customer IDs: 21,150 / 6 = 3,525

It returns this same value for every outer row because the subquery is no longer correlated. The query runs without errors, making the mistake difficult to notice.

A subquery written inside FROM is called a derived table. The outer query can use the result of that subquery as if it were a table. We have already used derived tables in P07 and P09 to prepare data before joining, filtering, or aggregating it.

There are two important rules to remember:

1. The outer query can access only the columns selected by the derived table. Columns not included in its SELECT are not available outside it. Computed columns should have clear aliases so the outer query can reference them.
2. A derived table should have an alias. For example, AS top_orders gives the subquery result a name. Some databases require this alias, while others make it optional.

A derived table also has its own WHERE, ORDER BY, GROUP BY and LIMIT clauses. This matters because filtering before limiting rows can produce a different result from filtering afterward.

An example: Finding the largest paid orders

Marketing wants to find the three largest paid orders.

An analyst already has a query that returns the three largest orders overall. They place it inside a derived table and filter for paid orders in the outer query.

SELECT order_id, amount, status
FROM (
    SELECT order_id, amount, status
    FROM orders
    ORDER BY amount DESC, order_id
    LIMIT 3
) AS top_orders
WHERE status = 'paid'
ORDER BY amount DESC, order_id;

The query returns only two orders, even though there are at least three paid orders.

Why does this happen?

The inner query first selects the three largest orders, regardless of their status:

• Order 1004: 8,500, cancelled.
• Order 1003: 3,600, paid.
• Order 1006: 3,600, paid.

The outer query then applies WHERE status = 'paid', removing order 1004. Only two rows remain. Order 1007, which is paid and worth 3,200, cannot appear because it was excluded by the inner LIMIT 3. The important point is that the inner query’s LIMIT has already restricted the available rows before the outer filter is applied.

The fix: Filter before limiting

To find the three largest paid orders, the status condition must be applied before selecting the top three.

SELECT order_id, amount, status
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, order_id
LIMIT 3;

Now SQL considers only paid orders before sorting and limiting the result. The order of operations matches the requirement.

Why are derived tables useful?

Derived tables become especially useful when we need to perform one operation before another. For example, suppose we need to calculate total spending for each customer and then find customers whose totals exceed a particular amount.

The inner query can calculate each customer’s total using GROUP BY. The outer query can then filter those totals using WHERE.

This separates the problem into two steps:

1. Calculate the result at the required level of detail.
2. Apply additional conditions to that result.

We will use this approach in the next section.

Database note: Derived-table alias requirements differ between engines. DuckDB, SQLite, Oracle and PostgreSQL 16 or later allow some derived tables without aliases, while MySQL and SQL Server require them.

Finance wants to find customers whose total order amount is greater than the average customer total.

We know how to calculate each customer’s total using GROUP BY. An analyst writes the following query, using the average order amount from section 02 as the benchmark.

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (
    SELECT AVG(amount)
    FROM orders
)
ORDER BY customer_id;

The query runs successfully, but it answers a different question from the one Finance asked.

What’s wrong with this query?

Look at the two values being compared.

• The outer query calculates total spending per customer: SUM(amount)
• The subquery calculates the average amount of an individual order: AVG(amount)

These are not the same kind of measurement. The average order amount is approximately 3,478.57, while a customer’s total can include several orders. This makes it easier for customer totals to exceed the benchmark, even when those customers are not above average in total spending. For example, Carla has only one order worth 3,600. Her total is greater than the average order amount of 3,478.57, so the query includes her.

But Finance asked whether Carla’s total is greater than the average total spending per customer, which is a different benchmark.

The correct approach: Compare values at the same level

We need to compare each customer’s total order amount to the average of all customer totals.

This requires two levels of aggregation:

1. Calculate SUM(amount) for each customer.
2. Calculate AVG(total_amount) across those customer totals.

We cannot simply nest both aggregate functions in the same query in most SQL engines.

SELECT AVG(SUM(amount)) AS avg_customer_total
FROM orders
GROUP BY customer_id;

Step 1: Calculate total spending per customer

We first need one row per customer, containing that customer’s total order amount. There is one important detail: guest orders have a NULL customer_id. Since guest orders may belong to different people, we should not treat all guest orders as one customer.

For this calculation, we will exclude orders without a customer ID.

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id;

The customer totals are:

These are the values we need to average.

Step 2: Calculate the average customer total

We can place the grouped query inside a derived table and calculate the average of its results.

SELECT AVG(total_amount) AS avg_customer_total
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) AS customer_totals;

The average customer total is: (2,250 + 6,800 + 3,600 + 8,500) / 4 = 5,287.5

This is the correct benchmark for the requirement.

Step 3: Find customers above that average

We now need to compare each customer’s total with the average customer total. Both values come from the same grouped data.

SELECT
    customer_id,
    total_amount
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) AS customer_totals
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_amount
        FROM orders
        WHERE customer_id IS NOT NULL
        GROUP BY customer_id
    ) AS again
)
ORDER BY customer_id;

Only Ben and Dev have totals greater than 5,287.5. Carla is now correctly excluded because her total of 3,600 is below the average customer total.

What is the limitation of this approach?

The query is correct, but notice that we wrote the same customer-total calculation twice:

• Once to produce the customer totals.
• Again to calculate their average.

This makes the query longer and creates a maintenance problem. If the definition of customer spending changes, both copies must be updated consistently.

In section 07, we will use a common table expression to define the customer totals once and reuse them.

What about customers with no orders?

Our current calculation includes only customers who have at least one order. Esha has never ordered, so she does not appear in the grouped totals. If Finance wants every registered customer included in the average, with zero spending for customers without orders, we need to start from customers and use a LEFT JOIN. Using COALESCE to give Esha a total of zero would change the average customer total from 5,287.5 to 4,230.

For this dataset, Ben and Dev would still be above average, but the benchmark itself would be different. This is why we must decide which customers belong in the calculation before computing the average.

In section 06, we calculated customer totals twice because both the main query and the benchmark needed the same result. A common table expression (CTE) lets us give that calculation a name and reference it elsewhere in the same SQL statement. A CTE is defined using the WITH clause, which appears before the main query.

Let’s rewrite the previous example using a CTE.

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_amount
FROM customer_totals
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM customer_totals
)
ORDER BY customer_id;

The result is the same as before, but the query is easier to follow. The WITH clause defines customer_totals, a named result containing one row per customer. The main query then uses that result in two places:

• FROM customer_totals provides the totals to report.
• The subquery calculates the average of those totals.

We no longer need to repeat the original aggregation.

Using multiple CTEs

A WITH clause can also contain multiple CTEs, separated by commas. This lets us break a larger query into smaller steps, where each step can use results defined earlier.

Suppose Finance wants to show the average customer total alongside each above-average customer.

We can define two CTEs:

1. customer_totals calculates spending per customer.
2. benchmark calculates the average of those totals.

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
),
benchmark AS (
    SELECT AVG(total_amount) AS avg_total
    FROM customer_totals
)
SELECT
    t.customer_id,
    t.total_amount,
    b.avg_total
FROM customer_totals AS t
CROSS JOIN benchmark AS b
WHERE t.total_amount > b.avg_total
ORDER BY t.customer_id;

How do the CTEs work together?

The first CTE produces customer totals. The second CTE reads those totals and calculates a single average value. The main query uses CROSS JOIN to add the benchmark to each customer-total row. Because benchmark contains exactly one row, the CROSS JOIN adds the average without multiplying the number of customer rows. Finally, WHERE keeps only customers whose totals exceed the benchmark. This structure makes the calculation easier to understand because each CTE has one clear responsibility.

A CTE can also declare its output column names directly:

WITH customer_totals (customer_id, total_amount) AS (
    ...
)

These declared names are used for the CTE’s columns.

Database note: DuckDB, PostgreSQL, SQLite, Oracle and SQL Server support WITH. MySQL supports CTEs from version 8.0. In SQL Server scripts, you may see ;WITH because the preceding statement must be properly terminated.

After running the report, an analyst wants to inspect the customer totals separately. They add another SELECT statement after the original query.

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_amount
FROM customer_totals
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM customer_totals
)
ORDER BY customer_id;
 
SELECT *
FROM customer_totals
ORDER BY customer_id;

CTEs make queries easier to understand, but their names can also introduce subtle mistakes. Suppose Finance wants to report the number of paid orders and the total number of orders.

An analyst creates a CTE containing only paid orders but gives it the same name as the original table: orders.

WITH orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT
    COUNT(*) AS paid_orders,
    (SELECT COUNT(*) FROM orders) AS all_orders
FROM orders;

The fix: Use a descriptive CTE name

Give the CTE a name that describes what its rows represent and does not conflict with an existing table.

WITH paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT
    COUNT(*) AS paid_orders,
    (SELECT COUNT(*) FROM orders) AS all_orders
FROM paid_orders;

Now the distinction is clear:

• paid_orders refers to the filtered CTE.
• orders refers to the original table.

The query correctly reports 5 paid orders out of 7 total orders.

Why does naming matter in larger queries?

A CTE name can affect every later reference to that name in the statement. If we name a CTE orders at the beginning of a long query, someone adding another join farther down may accidentally read the CTE instead of the original table. This can produce incorrect results without an obvious error.

Database note: DuckDB, PostgreSQL and MySQL permit the name-shadowing behaviour shown here.

In section 07, the query referenced customer_totals twice.

That raises an important question: Does the database calculate a CTE once and reuse its result, or run it separately every time the name appears?

The answer depends on the database engine and its optimizer. There are two common approaches.

Materialization

The database calculates the CTE once, keeps its intermediate result for the statement, and lets multiple references read those rows.

Inlining

The database replaces each reference to the CTE with its underlying query, allowing the optimizer to plan each occurrence separately.

For a deterministic query, both approaches should produce the same logical result, although performance may differ. The distinction becomes visible when the CTE calculates a value that can change between executions.

An example using random()

Consider this query:

WITH draw AS (
    SELECT random() AS x
)
SELECT a.x = b.x AS same_value
FROM draw AS a
CROSS JOIN draw AS b;

Here, random() generates a random number. In DuckDB, this example materialises the CTE, so both references read the same generated value. Therefore, a.x = b.x evaluates to TRUE.

Now consider the same query with NOT MATERIALIZED:

WITH draw AS NOT MATERIALIZED (
    SELECT random() AS x
)
SELECT a.x = b.x AS same_value
FROM draw AS a
CROSS JOIN draw AS b;

In this example, the CTE is inlined, and the two references evaluate random() separately. They produce different values, so the comparison returns FALSE in the demonstrated run. The important point is that referencing a CTE twice does not universally guarantee that both references read a single stored result.

How do different databases handle CTEs?

These are engine-dependent behaviors, not guarantees that every query will follow one strategy.

In P08, we used IN to check whether a value appears in a list returned by a subquery. SQL also provides ANY and ALL, which let us compare a value with multiple values returned by a subquery.

The difference is straightforward:

• ANY returns TRUE when the comparison is satisfied by at least one value in the list.
• ALL returns TRUE when the comparison is satisfied by every value in the list.

Both work with comparison operators such as =, >, < and <>.

Example: Comparing orders with Asha’s orders

Asha has placed two orders:

Suppose we want to find orders whose amounts are greater than at least one of Asha’s orders.

We can use > ANY:

SELECT order_id, amount
FROM orders
WHERE amount > ANY (
    SELECT amount
    FROM orders
    WHERE customer_id = 1
)
ORDER BY order_id;

The subquery returns two amounts: 1500 and 750. > ANY keeps an order if its amount is greater than at least one of them. For example, order 1001 is worth 1,500. It is not greater than 1,500, but it is greater than 750, so it qualifies. Only order 1005, worth 750, is excluded because its amount is not greater than either value.

What changes with ALL?

Now suppose we want to find orders whose amounts are greater than both of Asha’s orders.

We can use > ALL:

SELECT order_id, amount
FROM orders
WHERE amount > ALL (
    SELECT amount
    FROM orders
    WHERE customer_id = 1
)
ORDER BY order_id;

This time, an order must be greater than both 1,500 and 750. Order 1001 no longer qualifies because 1,500 is not greater than itself.

Marketing wants to recommend products Ben has never purchased. Ben has bought two products:

• Mechanical Keyboard
• Monitor Stand

An analyst interprets “a product that is not equal to any of Ben’s products” and writes:

SELECT order_id, product
FROM orders
WHERE product <> ANY (
    SELECT product
    FROM orders
    WHERE customer_id = 2
)
ORDER BY order_id;

The fix: Use <> ALL

The requirement is to find products that do not match any of Ben’s purchased products. That means each product must be different from every value returned by the subquery. We can express this using <> ALL:

SELECT order_id, product
FROM orders
WHERE product <> ALL (
    SELECT product
    FROM orders
    WHERE customer_id = 2
)
ORDER BY order_id;

Now Mechanical Keyboard and Monitor Stand are excluded because Ben has already purchased them. Two useful equivalences are:

This also means <> ALL has the same NULL behavior as NOT IN, which we studied in P08.

What happens when the subquery returns no rows?

There is another important behaviour of ALL. Suppose we want orders whose amounts are greater than every order Esha has placed. Esha has never placed an order, so the subquery returns no rows.

SELECT order_id, amount
FROM orders
WHERE amount > ALL (
    SELECT amount
    FROM orders
    WHERE customer_id = 5
)
ORDER BY order_id;

Surprisingly, all seven orders qualify. This happens because ALL over an empty result is TRUE. There is no value in Esha’s order list that violates the comparison. This is another example of the empty-set behaviour we explored in section 09 of P08.

Why not use MAX instead?

It may seem that amount > ALL (...) is equivalent to comparing an amount with the maximum value in the subquery. But they differ when the subquery is empty. Consider:

SELECT order_id, amount
FROM orders
WHERE amount > (
    SELECT MAX(amount)
    FROM orders
    WHERE customer_id = 5
)
ORDER BY order_id;

Result: No rows.

Since Esha has no orders, MAX(amount) returns NULL. A comparison such as amount > NULL evaluates to UNKNOWN, so no orders qualify. This gives us an important distinction:

• > ALL over an empty result evaluates to TRUE.
• > (SELECT MAX(...)) over an empty input evaluates to UNKNOWN because the maximum is NULL.

The two approaches can also differ when the subquery contains NULL values. MAX ignores NULL, while comparisons using ALL follow SQL’s three-valued logic.

Database note: DuckDB, PostgreSQL, MySQL, SQL Server and Oracle support ANY and ALL subquery comparisons. SQLite does not support them directly.

Sometimes we need to retrieve multiple columns from the rows related to each outer row. For example, Sales wants to show every customer along with their largest order, including its order ID and amount.

A correlated scalar subquery can calculate a single value for each customer, but a scalar subquery cannot directly return both order_id and amount as separate columns. A regular derived table can return several columns, but it generally cannot reference columns from an earlier table in the same FROM clause.

LATERAL provides a way to do both.

A lateral subquery is a subquery in FROM that can reference columns from tables appearing before it in the same FROM clause. It can return zero, one, or multiple rows for each outer row.

Example: Each customer’s largest order

SELECT
    c.customer_name,
    top_order.order_id,
    top_order.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT
        o.order_id,
        o.amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.amount DESC, o.order_id
    LIMIT 1
) AS top_order ON true
ORDER BY c.customer_id;

How does LATERAL work?

For each customer, the lateral subquery:

1. Finds orders belonging to that customer.
2. Sorts those orders by amount, highest first.
3. Keeps the first order using LIMIT 1.

For Asha, the subquery considers orders 1001 and 1005. Since 1001 has the larger amount, it is selected. For Ben, order 1006 is worth 3,600, which is greater than order 1002’s amount of 3,200. Therefore, 1006 is selected. Carla and Dev each have only one order, so those orders are returned. Esha has no orders, so her subquery returns no rows.

However, the outer query uses LEFT JOIN LATERAL, which preserves every customer. Esha therefore appears with NULL order details.

Why use ON true?

Notice the join condition: ON true. The matching condition is already inside the lateral subquery: WHERE o.customer_id = c.customer_id

The lateral subquery finds the relevant orders for the current customer. ON true allows its returned rows to join without adding another matching condition. Because this is a LEFT JOIN, customers whose subqueries return no rows are still included.

Why include order_id in ORDER BY?

Suppose two orders have the same amount. Without another sorting column, SQL might select either order as the largest. Adding order_id as a tie-breaker makes the choice predictable. When amounts are equal, the smaller order ID is selected first.

Sales changes the requirement: Show each customer’s two largest orders, including customers who have fewer than two orders.

The analyst changes LIMIT 1 to LIMIT 2.

SELECT
    c.customer_name,
    top_orders.order_id,
    top_orders.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT
        o.order_id,
        o.amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.amount DESC, o.order_id
    LIMIT 2
) AS top_orders ON true
ORDER BY
    c.customer_id,
    top_orders.amount DESC,
    top_orders.order_id;

Database support

We will revisit finding the top rows within each group when we study window functions in P15 and P18. Those approaches can often be more efficient for large datasets.

Subquery and CTE mistakes do not always produce errors. A query may return a believable number while using the wrong rows, comparing values at different levels of detail, or referencing a different table than intended.

The table below highlights common problems and their likely causes.

How to investigate a subquery

When a query produces an unexpected result, start with three checks.

1. Check how many rows the subquery returns. For scalar subqueries, ask whether the result can contain zero, one, or multiple rows. Do not assume a condition identifies one row unless the table design guarantees it.
2. Check the subquery independently. For an uncorrelated subquery, run it on its own and inspect the result. For a correlated subquery, choose a specific outer row and replace the outer references with that row’s values. For example, test Asha’s average using customer_id = 1 before relying on the correlated calculation.
3. Check the level of detail and column references. Confirm that the benchmark represents the same kind of value as the one being compared. Also check that aliases and CTE names reference the intended tables, especially when the same table appears more than once.

Many SQL problems can be solved using more than one approach. For example, we can calculate a customer’s average order amount using a correlated scalar subquery or by joining orders to a table of customer averages.

Neither approach is always better. The right choice depends on what the intermediate result needs to contain and how it will be used.

Which approach should you use?

Example: Correlated subquery vs JOIN

In section 04, we calculated the average order amount for each customer using a correlated scalar subquery. We can produce the same result by calculating customer averages first and joining them back to the individual orders.

SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    a.customer_avg
FROM orders AS o
LEFT JOIN (
    SELECT
        customer_id,
        AVG(amount) AS customer_avg
    FROM orders
    GROUP BY customer_id
) AS a
    ON a.customer_id = o.customer_id
ORDER BY o.order_id;

The result is identical to the correlated subquery from section 04. The derived table calculates one average per customer_id. The outer query then joins those averages to the individual orders. We use a LEFT JOIN so that guest order 1007 remains in the result. Although the derived table contains a group for NULL customer IDs, the equality join does not match NULL with NULL. Therefore, order 1007 receives a NULL customer_avg.

When is JOIN more useful?

Suppose we need both the average order amount and the number of orders for each customer. A grouped derived table can calculate both values together:

SELECT
    customer_id,
    AVG(amount) AS customer_avg,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

We can then join these results to the orders table. This is often clearer than writing multiple correlated scalar subqueries, one for each calculated value. However, when we need just one value, a correlated scalar subquery may be shorter and easier to understand.

Derived table vs CTE

A derived table is useful when we need one intermediate result directly inside another query. A CTE is often easier to read when the query involves several logical steps or when an intermediate result is referenced multiple times.

For example, instead of nesting several levels of derived tables, we can write:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
),
benchmark AS (
    SELECT AVG(total_amount) AS avg_total
    FROM customer_totals
)
SELECT *
FROM benchmark;

Each step has a meaningful name, making the calculation easier to follow. Remember, though, that a CTE is available only within the statement that defines it.

When should we use a temporary table?

Suppose several separate reports need to use the same customer totals. A CTE cannot be shared across those statements without repeating its definition. A temporary table allows us to calculate the totals once and store the result for later statements in the same session.

For example:

CREATE TEMPORARY TABLE customer_totals AS
SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

We can now run separate queries against customer_totals without repeating the aggregation. Temporary tables are also useful when multiple steps must operate on the same stored snapshot of intermediate data. The exact syntax and lifetime rules vary by database. For example, SQL Server commonly uses a local temporary table such as #customer_totals.

What about views?

A view serves a different purpose. When we create a view, the database stores the query definition rather than a fixed set of result rows.

For example:

CREATE VIEW customer_totals AS
SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

Other queries can reference the view as if it were a table. Unlike a temporary table, a regular view does not store a snapshot of the results. Its underlying query is evaluated when the view is used, subject to the database’s optimisation rules. Views are useful when several reports need a consistent definition of the same calculation.

Three rules for writing readable queries

1. Use a short subquery when only one calculation is needed. Avoid introducing several CTEs for a simple comparison.
2. Use CTEs to organise multi-step queries. Give each step a meaningful name and place definitions in the order they are needed.
3. Keep each step at a clear level of detail. A CTE named customer_totals should ideally contain one row per customer, while paid_orders should represent paid-order rows.

No approach is automatically faster. Database optimisers can often transform subqueries, CTEs and joins into similar execution plans. Choose the structure that expresses the requirement clearly, then investigate performance when necessary.

Subqueries let one SQL query use the result of another query as a value, a list, or a table. CTEs help organise these calculations into named steps, making complex queries easier to read and maintain. The most important part is understanding what each intermediate query returns: how many rows it can produce, what those rows represent, and whether the outer query’s conditions affect it.

Be especially careful when comparing aggregates, referencing outer columns, handling empty results, and reusing CTEs.

Next, in P11, we will explore dates and timestamps. We will reuse scalar subqueries and CTEs to calculate reference periods, group data over time, and build date-based reports.