Engineering/Database

How Subqueries and CTEs Work in SQL

Isaac S. Lee 2026. 8. 4. 21:26

As SQL queries become more complex, a single table is often not enough to produce the result we need.

Suppose we want to answer the following question:

Which orders have a total amount greater than the average order amount?

Before identifying those orders, the database must first calculate the average.

SELECT AVG(total_amount)
FROM orders;

It must then compare each order with that value.

SQL allows us to express both steps in a single statement:

SELECT
    order_id,
    customer_id,
    total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

The SELECT statement inside the parentheses is called a subquery.

A subquery is a query nested inside another SQL statement. It produces a value or a set of rows that the outer query can use.

Conceptually, the query works in two stages:

Inner query
→ Calculate the average order amount

Outer query
→ Return orders above that average

A Common Table Expression, or CTE, can express the same logic in a different form:

WITH order_average AS (
    SELECT AVG(total_amount) AS average_amount
    FROM orders
)
SELECT
    o.order_id,
    o.customer_id,
    o.total_amount
FROM orders o
CROSS JOIN order_average a
WHERE o.total_amount > a.average_amount;

Both subqueries and CTEs help divide complex SQL into logical steps. However, they differ in where they are written, how their results are referenced, how clearly they communicate intent, and how the query optimizer may execute them.

There are two perspectives worth keeping in mind:

What intermediate result does the subquery or CTE logically produce?

How does the database physically calculate and use that result?

A subquery provides a result to another query

Suppose we have the following orders table:

order_idcustomer_idorder_statustotal_amount

5001 1001 PAID 120,000
5002 1001 CANCELED 80,000
5003 1002 PAID 150,000
5004 1003 PAID 50,000

Assume that the average order amount is 100,000.

The inner query returns one value:

SELECT AVG(total_amount)
FROM orders;

Result:

100000

The outer query uses that value as a comparison target:

SELECT
    order_id,
    customer_id,
    total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

The result contains the orders above the average:

order_idcustomer_idtotal_amount

5001 1001 120,000
5003 1002 150,000

A subquery can return several different shapes of data:

  • a single value
  • a single row
  • multiple rows from one column
  • multiple rows and columns that behave like a table

The shape returned by the subquery must match what the outer query expects.

Scalar subqueries return one value

A subquery that returns exactly one row and one column is called a scalar subquery.

SELECT
    order_id,
    total_amount,
    (
        SELECT AVG(total_amount)
        FROM orders
    ) AS average_amount
FROM orders;

The average is displayed next to every order:

order_idtotal_amountaverage_amount

5001 120,000 100,000
5002 80,000 100,000
5003 150,000 100,000
5004 50,000 100,000

A scalar subquery can be used anywhere a single value is valid, including:

  • the SELECT list
  • a comparison in WHERE
  • a condition in HAVING
  • an expression in ORDER BY

If the subquery unexpectedly returns several rows, it cannot be treated as a single value.

Consider this query:

SELECT
    order_id
FROM orders
WHERE customer_id = (
    SELECT customer_id
    FROM customers
);

If the customers table contains several customers, the inner query returns multiple IDs. The equality operator expects one value, so the query is invalid for that result shape.

When multiple values are expected, operators or constructs such as IN, EXISTS, or JOIN may be more appropriate.

Multi-row subqueries return a set of values

Suppose we want to find the customers who have at least one paid order.

The inner query can return the customer IDs associated with paid orders:

SELECT customer_id
FROM orders
WHERE order_status = 'PAID';

Its result might be:

1001
1002
1003

The outer query can use IN to test whether each customer ID belongs to that set:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_status = 'PAID'
);

Conceptually:

Customer 1001
→ Present in the inner result
→ Include the customer

Customer 1002
→ Present in the inner result
→ Include the customer

IN is a natural choice when a value should be compared with a set of possible values.

Special care is required with NOT IN, however.

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

If the subquery result contains NULL, SQL’s three-valued logic may produce an unexpected result. A comparison against a set containing an unknown value cannot always be evaluated as simply true or false.

When the requirement is to find rows for which no related row exists, NOT EXISTS often expresses the intention more safely and clearly.

A correlated subquery refers to the current outer row

The average-order subquery used earlier is independent of the outer query:

SELECT AVG(total_amount)
FROM orders;

It calculates the same average regardless of which outer order is currently being examined.

Now consider a different requirement:

Return orders whose amount is greater than the average for that particular customer.

Isaac’s orders must be compared with Isaac’s average. Sophie’s orders must be compared with Sophie’s average.

SELECT
    o.order_id,
    o.customer_id,
    o.total_amount
FROM orders o
WHERE o.total_amount > (
    SELECT AVG(o2.total_amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

The inner query refers to o.customer_id, which belongs to the outer query:

WHERE o2.customer_id = o.customer_id

This is called a correlated subquery.

Logically, it can be understood as follows:

Examine order 5001
→ customer_id is 1001
→ Calculate the average for customer 1001
→ Compare order 5001 with that average

Examine order 5002
→ customer_id is 1001
→ Compare it with customer 1001's average

Examine order 5003
→ customer_id is 1002
→ Compare it with customer 1002's average

This explanation can make it seem as though the database must always execute the inner query from the beginning for every outer row.

That is the logical meaning, but it is not necessarily the physical execution strategy.

The optimizer may transform the query by:

  • rewriting it as a JOIN
  • calculating grouped results first
  • using a semi-join strategy
  • caching reusable results
  • materializing an intermediate result

A correlated subquery is therefore not automatically slow.

It can become expensive when the outer query produces many rows and the inner lookup must be repeated without an efficient access path. The only reliable way to judge its performance is to inspect the execution plan and measure the actual amount of work.

EXISTS asks whether a matching row exists

Consider the following requirement:

Return customers who have placed at least one order.

We could use a JOIN:

SELECT DISTINCT
    c.customer_id,
    c.customer_name
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

If Isaac has two orders, the JOIN initially produces two Isaac rows. DISTINCT is then needed to reduce them to one customer row.

EXISTS expresses the requirement more directly:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The inner query is not being used to return order data.

It answers one question for each customer:

Does an order exist for Isaac?
→ Yes
→ Include Isaac

Does an order exist for Olivia?
→ No
→ Exclude Olivia

The value 1 in SELECT 1 is not special. EXISTS checks only whether at least one matching row can be found.

The following form has the same logical meaning:

WHERE EXISTS (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

SELECT 1 is commonly used because it makes the intent clear: the actual column values are not needed.

NOT EXISTS finds rows without a relationship

To find customers who have never placed an order, use NOT EXISTS:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The question is:

Is there no order associated with this customer?

The same requirement can be expressed with a LEFT JOIN:

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A modern optimizer may transform both forms into similar anti-join strategies.

Neither syntax is universally faster.

The better choice depends on:

  • whether the SQL clearly expresses the business requirement
  • how NULL values are handled
  • how many rows the execution plan processes
  • which indexes are available
  • how the optimizer rewrites the query

When only existence or non-existence matters, EXISTS and NOT EXISTS usually communicate that intention clearly.

A subquery in FROM behaves like a temporary table

A subquery can also appear in the FROM clause.

Suppose we first calculate the number and total value of orders for each customer:

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_amount
FROM orders
GROUP BY customer_id;

That result can then be joined to the customer table:

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(os.order_count, 0) AS order_count,
    COALESCE(os.total_amount, 0) AS total_amount
FROM customers c
LEFT JOIN (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) os
  ON os.customer_id = c.customer_id;

The subquery inside FROM behaves like a table within the scope of the statement.

It is often called a derived table or an inline view.

Conceptually:

Read orders
→ Aggregate them by customer
→ Name the result os
→ Join os with customers

This technique can be useful when directly joining several one-to-many tables would multiply rows unnecessarily.

Aggregating each source to the required grain before joining can reduce the size of the intermediate result.

However, writing a subquery in FROM does not necessarily mean that the database must create a complete temporary table first.

The optimizer may:

  • merge the derived table into the outer query
  • materialize it as an intermediate result
  • push outer conditions into the subquery
  • reorder joins
  • eliminate unnecessary columns or rows

The written SQL structure and the physical execution plan may therefore differ.

A CTE is a named intermediate query

Deeply nested subqueries can become difficult to read.

Consider this query:

SELECT
    c.customer_name,
    os.order_count,
    os.total_amount
FROM customers c
LEFT JOIN (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM orders
    WHERE order_status = 'PAID'
    GROUP BY customer_id
) os
  ON os.customer_id = c.customer_id
WHERE os.total_amount >= 100000;

A CTE allows us to give the intermediate result a meaningful name.

CTE stands for Common Table Expression.

WITH paid_order_summary AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM orders
    WHERE order_status = 'PAID'
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    s.order_count,
    s.total_amount
FROM customers c
JOIN paid_order_summary s
  ON s.customer_id = c.customer_id
WHERE s.total_amount >= 100000;

paid_order_summary is not a permanent table.

It is a named result set that exists only within the current SQL statement.

The query now exposes its logical stages:

Stage 1
Aggregate paid orders by customer

Stage 2
Join the summary with customers

Stage 3
Keep customers whose paid total is at least 100,000

The main advantage of a CTE is not necessarily speed.

Its most immediate benefit is that it can make the logical structure of a complex query easier to understand.

Multiple CTEs can express a data-processing pipeline

A statement may define several CTEs:

WITH paid_orders AS (
    SELECT
        order_id,
        customer_id,
        total_amount
    FROM orders
    WHERE order_status = 'PAID'
),
customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    t.order_count,
    t.total_amount
FROM customer_totals t
JOIN customers c
  ON c.customer_id = t.customer_id;

The data flow is visible from the names:

paid_orders
→ Paid orders only

customer_totals
→ Paid orders grouped by customer

Final SELECT
→ Add customer information

This can be easier to follow than several levels of nested parentheses.

However, dividing every small expression into a separate CTE can make the query fragmented. A CTE is most useful when it represents a meaningful logical stage rather than an arbitrary temporary step.

A CTE is not always a materialized temporary table

A common misunderstanding is that every CTE works like this:

Execute the WITH query
→ Store its result in a temporary table
→ Read that table in the final query

That may happen, but it is not guaranteed.

Depending on the database, version, and query structure, the optimizer may either inline the CTE or materialize it.

Inlining

The CTE is merged into the surrounding query and optimized as part of one larger execution plan.

CTE definition
+
Outer query

→ One integrated plan

When a CTE is inlined, outer predicates may be pushed into it, allowing irrelevant rows to be removed earlier.

Materialization

The CTE is evaluated first and stored as an intermediate result.

Execute CTE
→ Store intermediate result
→ Read it from the outer query

Materialization may be useful when a complex result is referenced repeatedly.

It can also be expensive when the intermediate result is large because the database must write, store, and read that result.

This is why neither of the following statements is universally true:

CTEs are faster than subqueries.

CTEs are slower because they always create temporary tables.

Performance depends on whether the optimizer inlines or materializes the CTE, how often it is referenced, and how large the intermediate result becomes.

Recursive CTEs build the next result from the previous one

A normal CTE defines one result set.

A recursive CTE refers back to its own previous result to produce the next level.

A common use case is hierarchical data.

Consider the following department table:

department_iddepartment_nameparent_department_id

1 Headquarters NULL
2 Development 1
3 Platform Team 2
4 AI Team 2
5 Sales 1

Suppose we want to return Headquarters and every department beneath it.

WITH RECURSIVE department_tree AS (
    SELECT
        department_id,
        department_name,
        parent_department_id,
        0 AS depth
    FROM departments
    WHERE department_id = 1

    UNION ALL

    SELECT
        d.department_id,
        d.department_name,
        d.parent_department_id,
        dt.depth + 1
    FROM departments d
    JOIN department_tree dt
      ON d.parent_department_id = dt.department_id
)
SELECT
    department_id,
    department_name,
    parent_department_id,
    depth
FROM department_tree;

A recursive CTE has two main parts.

Anchor query

The anchor query selects the starting point:

SELECT
    department_id,
    department_name,
    parent_department_id,
    0 AS depth
FROM departments
WHERE department_id = 1

The starting department is Headquarters.

Recursive query

The recursive query finds rows related to the previous result:

SELECT
    d.department_id,
    d.department_name,
    d.parent_department_id,
    dt.depth + 1
FROM departments d
JOIN department_tree dt
  ON d.parent_department_id = dt.department_id

Conceptually, the process is:

Iteration 1
Find Headquarters

Iteration 2
Find children of Headquarters
→ Development and Sales

Iteration 3
Find children of Development and Sales
→ Platform Team and AI Team

Stop when no new child rows are found

Recursive CTEs are useful for:

  • organization charts
  • category trees
  • folder structures
  • comments and replies
  • bills of materials
  • dependency relationships
  • graph traversal
  • number or date sequences

Recursive queries need a termination path

A recursive relationship can continue indefinitely if the data contains a cycle.

For example:

Department A reports to Department B
Department B reports to Department A

The traversal may repeatedly return to rows it has already visited.

Database systems often impose a maximum recursion depth, but the data model should also prevent invalid cycles when possible.

A depth condition can provide additional protection:

WHERE dt.depth < 10

However, a depth limit does not correct a fundamentally invalid hierarchy.

When using a recursive CTE, verify that:

  • the anchor row is clearly defined
  • every recursive step moves toward a new row
  • cycles cannot cause infinite repetition
  • the expected maximum depth is reasonable
  • the recursive join column is indexed appropriately

Subqueries, JOINs, and CTEs are not competing features

The same business requirement can often be expressed in several ways.

Suppose we want to find customers who have placed an order.

JOIN

SELECT DISTINCT
    c.customer_id,
    c.customer_name
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

A JOIN is natural when columns from both sides are needed.

EXISTS

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

EXISTS is natural when only the presence of a related row matters.

CTE

WITH customers_with_orders AS (
    SELECT DISTINCT customer_id
    FROM orders
)
SELECT
    c.customer_id,
    c.customer_name
FROM customers c
JOIN customers_with_orders co
  ON co.customer_id = c.customer_id;

A CTE can make the intermediate business concept explicit and reusable within the statement.

No form is always best.

RequirementNatural choice

Return columns from related tables JOIN
Test whether a related row exists EXISTS or NOT EXISTS
Compare against one calculated value Scalar subquery
Treat an aggregate result like a table Derived table or CTE
Name and organize complex stages CTE
Traverse hierarchical data Recursive CTE

SQL should first express the business requirement correctly and clearly. Performance should then be validated with the execution plan and realistic data.

How the optimizer may transform subqueries and CTEs

The written form of a query does not completely determine how it will be executed.

The optimizer may:

  • rewrite a subquery as a JOIN
  • transform IN into a semi-join
  • transform EXISTS into a semi-join
  • transform NOT EXISTS into an anti-join
  • merge a derived table into the outer query
  • inline a CTE
  • materialize a subquery or CTE
  • push outer predicates into an inner query
  • reorder related operations

For this reason, a query should not be judged solely by whether it contains a subquery or CTE.

When investigating performance, check the following.

Number of rows produced by the inner query

If an inner query produces millions of rows before being joined or filtered, the intermediate result may become expensive.

Repetition in a correlated subquery

Determine how many outer rows are processed and how often the correlated lookup is performed.

Available indexes

The columns used to connect the outer and inner queries may require an index.

CREATE INDEX idx_orders_customer
ON orders (customer_id);

This can support a correlated condition such as:

WHERE o.customer_id = c.customer_id

Inlining or materialization

Check whether the derived table or CTE is merged into the surrounding query or stored as a separate intermediate result.

Estimated row counts

The optimizer must estimate how many rows each stage will produce.

Incorrect estimates can lead to an inefficient join order, repeated lookup strategy, or materialization decision.

MariaDB and MySQL provide EXPLAIN and related detailed plan formats for investigating these decisions.

EXPLAIN
SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The key performance question is not:

Does this query contain a subquery?

It is:

How many rows does the database read, and which access path does it use to produce the result?

Practical guidelines for using subqueries and CTEs

Subqueries and CTEs are powerful, but excessive nesting or unnecessary fragmentation can make SQL harder to understand.

Give intermediate results meaningful names

A CTE name should describe the business meaning of its result.

WITH paid_order_summary AS (...)

This communicates more than:

WITH temp1 AS (...)

Avoid repeating the same calculation

If the same filtering or aggregation logic appears repeatedly, a named CTE may make the statement easier to understand.

However, the fact that the CTE is written once does not guarantee that it will be physically calculated only once. Check the execution plan.

Watch the outer row count of a correlated subquery

A correlated lookup may be efficient for a small outer result with a useful inner index.

It may become expensive when millions of outer rows trigger repeated searches.

Use EXISTS when only existence matters

If no columns from the related rows are needed, JOIN may create unnecessary duplicate combinations.

EXISTS often expresses the intention more accurately.

Replace deeply nested logic with meaningful stages

Several levels of nested subqueries may become easier to read when converted into a small number of well-named CTEs.

Do not divide a query into too many CTEs

A separate CTE for every minor expression can make the overall data flow harder to follow.

Each CTE should represent a meaningful transformation or business concept.

Measure with realistic data

Do not compare two query forms using only one elapsed-time measurement.

Also inspect:

  • rows read
  • rows returned
  • execution frequency
  • intermediate result size
  • temporary-storage use
  • index access
  • estimated rows versus actual rows

Key concepts for the Information Processing Engineer exam

ConceptKey idea

Subquery A SELECT statement contained inside another SQL statement
Scalar subquery Returns one row and one column
Multi-row subquery Returns several rows and can be used with IN, ANY, or ALL
Correlated subquery Refers to a row from the outer query
EXISTS Tests whether a matching row exists
NOT EXISTS Tests whether no matching row exists
Inline view A subquery used like a table in the FROM clause
CTE A named result set defined with WITH
Recursive CTE Repeatedly refers to its previous result
Materialization Calculates and stores an intermediate result before reading it again

Common exam topics include:

  • the number of values or rows returned by a subquery
  • single-row versus multi-row operators
  • outer references in correlated subqueries
  • EXISTS and NOT EXISTS
  • basic CTE syntax
  • anchor and recursive members of recursive CTEs
  • unexpected behavior of NOT IN when NULL is present

A useful way to solve a subquery question is to evaluate the inner query first and then apply its result to the outer query.

Understanding complex SQL begins with intermediate results

The most important part of subqueries and CTEs is not the parentheses or the WITH keyword.

It is understanding what each query produces and how that result is used by the next stage.

Subquery
→ Provides a value or row set to an outer query

Correlated subquery
→ Produces a result based on the current outer row

EXISTS
→ Tests whether a related row exists

CTE
→ Gives a meaningful name to an intermediate result

Recursive CTE
→ Uses the previous result to produce the next level

Writing a subquery or CTE does not mean that the database will execute the SQL exactly in the order it appears.

The optimizer may rewrite the query as a JOIN, merge stages, push predicates inward, or materialize an intermediate result.

The relationship can be summarized as follows:

Subqueries and CTEs divide a complex query into logical intermediate results, while the optimizer reorganizes those results into a physical execution plan.

Good SQL is not simply the shortest statement or the statement with the most CTEs.

Good SQL has clear stages, predictable intermediate results, correct treatment of existence and relationships, limited unnecessary repetition, and an execution plan that performs well on real data.

Understanding subqueries and CTEs therefore means more than knowing how to nest a SELECT statement or write a WITHclause.

It means understanding the shape of every intermediate result, how it relates to the outer query, and how the database may transform it during optimization.

References

저작자표시 비영리 변경금지 (새창열림)