Subqueries and Common Table Expressions

Subqueries place one query inside another. A common table expression (CTE) gives an intermediate query a name for the duration of one statement. Both help express a chain of relational transformations.

Prerequisites

You should be able to join, group, and describe the grain of every intermediate result.

Learning goals

• Distinguish scalar, table, and correlated subqueries.
• Use WITH to name and inspect transformation stages.
• Prevent accidental grain changes across stages.
• Understand that a CTE is a readability boundary, not a guaranteed materialized cache.

Choose a shape that matches the question

A scalar subquery must produce at most one row and one column. EXISTS asks whether a correlated subquery produces any row. A table subquery can appear in FROM and must have an alias in portable SQL.

CTEs are especially useful when each stage has a clear contract: first aggregate orders to customer grain, then join customer attributes, then filter the result. Recursive CTEs are a separate feature for traversing hierarchies and are not needed here.

Worked example

The inner aggregate produces one scalar: the largest known order amount. The outer query compares each row with it. This is portable Standard SQL.

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

order_id	amount
105	80.00

The CTE below establishes one row per customer before joining names. Filtering happens only after the customer-level total exists.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.customer_name, t.total_amount
FROM customer_totals AS t
JOIN customers AS c ON c.customer_id = t.customer_id
WHERE t.total_amount >= 80
ORDER BY c.customer_id;

customer_id	customer_name	total_amount
1	Ada	100.00
3	Cy	80.00

DuckDB and PostgreSQL may inline or materialize a CTE depending on the query and implementation rules. Do not rely on the written CTE boundary as an execution guarantee; inspect the plan when performance matters.

Limitations and common mistakes

• A scalar subquery returning multiple rows is an error, not an implicit choice of the first row.
• Reusing a CTE name does not make its grain obvious; document and test it.
• Deeply nested queries can be difficult to debug. Run each CTE body independently with a small fixture.
• Correlated subqueries are sometimes decorrelated by the optimizer, but do not assume a particular plan across engines.

Practice

Create an item_totals CTE at order grain, then compare it with orders.amount. Return any order where the totals disagree, including orders without items.

Next steps

The next lesson uses CTEs to filter results calculated by window functions.

Primary sources

Checked 2026-10-14: DuckDB WITH, DuckDB subqueries, and PostgreSQL WITH queries.