SQL Subqueries and CTEs: Derived Tables, Correlated Queries and Temporary Tables

A subquery is a query inside another query. A common table expression (CTE) is a named subquery declared up front with WITH. Both let you build an answer in steps: first compute each customer’s total, then compare it with the average, then label the result. This lesson covers every place a subquery can go, how correlated subqueries run, how to chain CTEs into a readable pipeline, and when a temporary table is the better tool.

Sample data

CREATE TABLE customers (customer_id INT PRIMARY KEY, full_name TEXT NOT NULL, country TEXT NOT NULL);
INSERT INTO customers VALUES
  (1, 'Asha Patel', 'IN'), (2, 'Ben Carter', 'GB'), (3, 'Chen Wei', 'SG'),
  (4, 'Diana Lopez', 'US'), (5, 'Ethan Brown', 'GB'), (6, 'Farah Khan', 'IN');

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  status      TEXT NOT NULL,
  amount      NUMERIC(10, 2) NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, '2025-06-01', 'delivered', 120.00),
  (102, 1, '2025-06-03', 'delivered',  35.50),
  (103, 2, '2025-06-03', 'cancelled',  80.00),
  (104, 3, '2025-06-04', 'shipped',   220.00),
  (105, 4, '2025-06-05', 'delivered', 220.00),
  (106, 4, '2025-06-07', 'placed',     15.00),
  (107, 5, '2025-06-07', 'delivered',  64.99),
  (108, 2, '2025-06-08', 'delivered',  80.00),
  (109, 1, '2025-07-02', 'delivered',  45.00),
  (110, 5, '2025-07-03', 'delivered', 150.00);

Subqueries in WHERE

A subquery in WHERE supplies the values a filter compares against. It answers questions such as “orders above the average” or “customers from countries where someone spent over 200” without hard-coding numbers.

Comparing with a single value

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

The subquery returns one value (the average, about 103.05), and each order is compared with it. You cannot write WHERE amount > AVG(amount) directly, because aggregates are not allowed in WHERE.

Comparing with a list: IN, EXISTS, ANY and ALL

When the subquery returns many rows, use a set operator:

-- Customers in any country where some order exceeded 200
SELECT full_name, country
FROM customers
WHERE country IN (
  SELECT c.country
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.amount > 200
)
ORDER BY full_name;

ALL is a neat way to express “the largest”: orders bigger than every order from GB customers:

SELECT order_id, amount
FROM orders
WHERE amount > ALL (
  SELECT o.amount FROM orders o JOIN customers c ON c.customer_id = o.customer_id
  WHERE c.country = 'GB'
)
ORDER BY order_id;

Pitfalls

• NOT IN with a subquery that can return NULL returns no rows; use NOT EXISTS (see the anti-join lesson). > ALL has the same weakness: a NULL in the list makes the comparison unknown.
• = (subquery) fails at run time if the subquery returns more than one row. Use IN when several rows are possible.
• > ALL over an empty subquery is true for every row, which can surprise you when a filter inside the subquery matches nothing.

In interviews

“Find employees earning more than the company average” and “the second-highest salary” (WHERE salary < (SELECT MAX(salary) ...)) are classic subquery-in-WHERE questions. Explain why the aggregate must be in a subquery, and mention the NOT IN NULL trap if a list is involved.

Subqueries in FROM (derived tables)

A subquery in FROM produces a temporary result set that the outer query treats like a table. This is called a derived table (or inline view). Its main use is two-stage aggregation: aggregate once, then aggregate or filter the result again.

SELECT ROUND(AVG(customer_total), 2) AS avg_spend_per_customer,
       MAX(customer_total)          AS top_customer_spend
FROM (
  SELECT customer_id, SUM(amount) AS customer_total
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
) AS totals;

The average order value and the average spend per customer are different metrics; the derived table makes the grain change explicit: first one row per customer, then one row overall.

How it works

• A derived table must have an alias in PostgreSQL (before version 16), MySQL and SQL Server; PostgreSQL 16 made it optional, but write one anyway for portability.
• Every computed column in it needs a name, because the outer query refers to it.
• A derived table cannot normally see columns of other tables in the same FROM; that requires LATERAL.
• Optimisers usually merge simple derived tables into the outer query, so they cost nothing extra; aggregates and DISTINCT inside them are computed first.

Filtering on a computed column

Because WHERE runs before SELECT, you cannot filter on an alias or a window function in the same query. Wrap the query and filter outside:

SELECT customer_id, customer_total
FROM (
  SELECT customer_id, SUM(amount) AS customer_total
  FROM orders
  GROUP BY customer_id
) AS t
WHERE customer_total >= 200
ORDER BY customer_total DESC;

(HAVING SUM(amount) >= 200 would work here too; the wrapper becomes necessary for window functions and for readability when the expression is long.)

Pitfalls

• Deeply nested derived tables (three or four levels) are hard to read and debug. Rewrite them as CTEs.
• Forgetting that the inner query sets the grain: joining a derived table of customer totals back to orders repeats the total on every order row.

In interviews

Expect “average number of orders per customer” or “average of daily totals”. The trap is computing AVG(amount) directly, which gives the average per order. State the two grains and aggregate twice.

Scalar subqueries

A scalar subquery returns exactly one column and at most one row, so it can be used anywhere a single value can: in SELECT, in WHERE, in CASE or in arithmetic.

SELECT order_id,
       amount,
       ROUND(100.0 * amount / (SELECT SUM(amount) FROM orders), 1) AS pct_of_all_revenue,
       amount - (SELECT AVG(amount) FROM orders)                   AS diff_from_avg
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

How it works

• Zero rows from a scalar subquery give NULL, not an error.
• More than one row is a run-time error:

-- ERROR: more than one row returned by a subquery used as an expression
SELECT order_id, (SELECT amount FROM orders WHERE customer_id = 1) AS amt
FROM orders;

The error depends on the data, so a query can work for months and fail when a second row appears. Make scalar subqueries return one row by construction: an aggregate (MAX, SUM), a filter on a unique key, or LIMIT 1 with an ORDER BY.

• An uncorrelated scalar subquery (one that does not reference the outer row) is computed once and reused, so (SELECT SUM(amount) FROM orders) above is cheap. A window function often expresses the same thing more directly: SUM(amount) OVER () gives the grand total on every row, as the window functions lesson shows.

Pitfalls

• A scalar subquery that is unique “today” but not guaranteed to be.
• Scalar subqueries in SELECT that are correlated (next section) and run per row on a large table.
• Long decimals: amount - (SELECT AVG(...)) inherits the average’s scale; round for presentation.

In interviews

Interviewers sometimes ask “what happens if this subquery returns two rows?” (an error) or “no rows?” (NULL). Showing the window-function alternative for percent-of-total is a plus.

Correlated subqueries

A correlated subquery refers to a column of the outer query, so its result depends on the current outer row. Logically it is re-evaluated once per outer row.

-- Orders larger than that customer's own average order
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
WHERE o.amount > (
  SELECT AVG(o2.amount)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id     -- correlation
)
ORDER BY o.customer_id, o.order_id;

Ben’s two orders are both 80.00, equal to his average, so neither is “larger”; Chen has a single order, which equals its own average.

Latest row per group

A correlated MAX is a classic way to keep the latest row per key:

SELECT o.customer_id, o.order_id, o.order_date
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id;

If a customer had two orders on their latest date, both would be returned. That may or may not be what you want; ROW_NUMBER() with a tiebreaker returns exactly one.

How engines run them

“Once per outer row” is the logical model. Optimisers usually decorrelate the query: they compute the inner aggregate for all customers once (like a GROUP BY in a derived table) and join it, or turn EXISTS into a semi-join. When they cannot, the query really does run as a nested loop, which is fast with an index on the correlated column and very slow on large tables without one. Columnar warehouses and Spark support only some correlated patterns, and Spark rewrites them into joins.

The explicit join form of the first query, which every engine handles well:

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

It returns the same three rows. A window function (AVG(amount) OVER (PARTITION BY customer_id)) is a third option.

Pitfalls

• Forgetting the correlation condition, which silently turns the subquery into an uncorrelated one (comparing with the overall average).
• Ambiguous column names: inside the subquery, an unqualified customer_id refers to the inner table if it has that column. Always qualify with aliases.
• Correlated scalar subqueries in SELECT on big tables, each one a separate lookup if not decorrelated.

In interviews

Be able to say what “correlated” means, how it is evaluated logically, that optimisers usually rewrite it as a join, and how you would rewrite it yourself (derived table join or window function). The classic prompt is “employees who earn more than their department’s average”.

Common table expressions (CTEs)

A CTE gives a subquery a name at the top of the statement with WITH name AS (...). The main query, and later CTEs, can then refer to it like a table. CTEs do not add power over derived tables (except recursion); they add readability: logic reads top to bottom, each step has a name, and a step used twice is written once.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT c.full_name, t.total_spend, t.orders
FROM customer_totals t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.total_spend > (SELECT AVG(total_spend) FROM customer_totals)
ORDER BY t.total_spend DESC;

customer_totals is used twice, once as a table and once inside the scalar subquery, but written once. Ben’s only non-cancelled order is 80.00, below the average of 190.10.

How it works

• A CTE exists only for the single statement that follows the WITH. It is not stored anywhere.
• You can name the columns after the CTE name: WITH totals (customer_id, spend) AS (...).
• Is a CTE computed once? It depends on the engine. Since PostgreSQL 12, a CTE referenced once (and without side effects) is inlined into the main query like a subquery; one referenced several times is computed once (materialised) by default. You can force either with AS MATERIALIZED or AS NOT MATERIALIZED. SQL Server always inlines, so a CTE referenced twice is evaluated twice. Snowflake, BigQuery and Spark decide per query. Never assume; read the plan.
• WITH RECURSIVE lets a CTE refer to itself to walk hierarchies or generate sequences. It is covered in the advanced part of this course.
• PostgreSQL also allows data-modifying statements in a CTE (WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved), which is handy for archiving rows in one statement.

Pitfalls

• Assuming a CTE is cached and referencing an expensive one several times in an engine that inlines it.
• Assuming a CTE is an optimisation fence. In older PostgreSQL versions (11 and earlier) it always was, so filters were not pushed into it; that is no longer true by default.
• Very long single-statement CTE chains with no intermediate checks. When a step is expensive or needs validating, persist it (next section).

In interviews

“What is the difference between a CTE and a subquery?” Answer: scope and capability are the same (one statement), except that CTEs can be recursive and can be referenced several times; the real difference is readability. Then add the engine-dependent materialisation point, which is what separates a strong answer.

Multiple chained CTEs

Several CTEs, separated by commas after one WITH, can each build on the ones before. This turns a complex query into a short pipeline of named steps, the same shape dbt models use.

WITH valid_orders AS (              -- 1. clean: drop cancelled orders
  SELECT order_id, customer_id, order_date, amount
  FROM orders
  WHERE status <> 'cancelled'
),
customer_stats AS (                 -- 2. aggregate to one row per customer
  SELECT customer_id,
         COUNT(*)        AS orders,
         SUM(amount)     AS total_spend,
         MAX(order_date) AS last_order_date
  FROM valid_orders
  GROUP BY customer_id
),
segmented AS (                      -- 3. apply business rules
  SELECT customer_id, orders, total_spend, last_order_date,
         CASE
           WHEN total_spend >= 200 AND orders >= 2 THEN 'loyal high value'
           WHEN total_spend >= 200                 THEN 'high value'
           ELSE 'standard'
         END AS segment
  FROM customer_stats
)
SELECT c.full_name,                 -- 4. present, keeping customers without orders
       COALESCE(s.orders, 0)          AS orders,
       COALESCE(s.total_spend, 0)     AS total_spend,
       COALESCE(s.segment, 'no orders') AS segment
FROM customers c
LEFT JOIN segmented s ON s.customer_id = c.customer_id
ORDER BY total_spend DESC, c.full_name;

Why this shape works

• Each step has one job: clean, aggregate, apply rules, present. A reviewer can check each in isolation.
• Debugging is easy: replace the final SELECT with SELECT * FROM customer_stats to inspect an intermediate step.
• Grain changes are visible: valid_orders is one row per order, customer_stats one row per customer.
• A later CTE can reference any earlier one, not only the previous step, but it cannot reference a later one.

Temporary tables: when a CTE is not enough

A temporary table is a real table that lives until the end of your session (or transaction), so you can reuse it across statements, index it and check it before continuing.

CREATE TEMPORARY TABLE recent_orders AS
SELECT * FROM orders WHERE order_date >= DATE '2025-07-01';

SELECT COUNT(*) AS recent_count FROM recent_orders;
SELECT customer_id, SUM(amount) AS recent_spend FROM recent_orders GROUP BY customer_id ORDER BY customer_id;

Use CTEs for clarity by default. Reach for a temporary table when an expensive intermediate result is reused by several statements, needs an index, or should be validated (row counts, duplicate checks) before the next step. In orchestrated pipelines the same idea often becomes a persisted staging table or an intermediate dbt model.

Pitfalls

• One enormous WITH chain doing the work of a whole pipeline, with no checkpoints. Split it where you would want to inspect or test the data.
• Naming CTEs t1, t2, t3. Name each after what it contains.
• Repeating the same filter in several CTEs instead of applying it once in the first step.
• Forgetting that temporary tables vanish at session end, which breaks jobs whose steps run in different sessions or connection-pool connections.

In interviews

Multi-step problems (“segment customers by spend and recency, then count per segment”) are much easier to solve aloud with chained CTEs: name each step as you go. If asked about performance, mention the materialisation question and temporary tables for reused expensive steps.

Practice questions

WITH per_customer AS (
  SELECT c.customer_id, COUNT(o.order_id) AS orders
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
)
SELECT AVG(orders) FROM per_customer;

Key takeaways

• Subqueries can appear in WHERE (filter values), FROM (derived tables) and SELECT (scalar values); each has its own rules.
• Use IN, EXISTS, ANY or ALL for multi-row subqueries in WHERE, and avoid NOT IN when NULLs are possible.
• Derived tables make two-stage aggregation and grain changes explicit.
• A scalar subquery must return at most one row: zero rows give NULL, several rows give an error.
• Correlated subqueries are logically per-row; optimisers usually rewrite them as joins, and you can too.
• Chain CTEs into named steps (clean, aggregate, apply rules, present); whether a CTE is computed once depends on the engine.
• Use a temporary table when an intermediate result is reused across statements, needs indexing or must be checked.