CTEs, Subqueries & Query Structure

Chapter 4 turns SQL from a pile of clauses into a readable plan. You will use CTEs, subqueries, and derived tables — and keep the last SELECT simple — so that even complex work stays easy to check and fix.

The key shift is to think in named row sets. A row set is just a batch of rows — the result of a query — that you give a name so you can use it later. Each CTE or subquery then answers one small question: which rows do I want, what does one row stand for, what am I measuring?

By the end you should be able to break a hard query into layers, choose between a CTE and a subquery on purpose, and walk someone through your answer step by step instead of just reciting syntax.

CTE

A batch of rows you name with WITH and then use in the main query — like a temporary table that exists only for this one query.

subquery

A SELECT written inside another query — used to filter rows, to work out a value to compare against, or to build a batch of rows for the outer query.

derived table

A subquery written in the FROM clause. The outer query reads it just like a table.

correlation

A subquery that looks back at the row the outer query is currently checking, so it runs again for each of those rows.

with paid_orders as (
  select order_id, buyer_id, total_amount
  from orders
  where status in ('paid', 'shipped')
)
select *
from paid_orders
order by order_id
limit 4;

A WITH block can chain several CTEs, and each one can build on the previous. This is how a multi-step calculation stays readable: every step has a name and a single job.

Here the first layer isolates paid and shipped orders, and the second aggregates revenue per buyer from that clean set. The final select just orders the answer.

Because each layer is independent, you can run any one of them on its own to check it before moving on.

with paid_orders as (
  select order_id, buyer_id, total_amount
  from orders
  where status in ('paid', 'shipped')
),
buyer_revenue as (
  select buyer_id, sum(total_amount) as revenue
  from paid_orders
  group by buyer_id
)
select buyer_id, revenue
from buyer_revenue
order by revenue desc, buyer_id;

The hidden superpower of CTEs is debugging. Before you trust the final answer, temporarily swap the final SELECT for a quick check against any layer.

Counting the rows in a layer, or checking that a key has no duplicates, turns a vague “the number looks off” into a precise “the filter kept 9 rows, as expected”.

The example below expects 9 paid or shipped orders out of 12. Ask what would happen if it came back as 12 instead: that would mean the status filter kept everything and is simply broken — and you would find that out now, while the query is five lines long, rather than after four more layers are stacked on top of it.

That timing is the whole point. A bug caught at the first layer costs you seconds. The same bug spotted in the final number, once the aggregates and joins are built on top of it, costs you an afternoon. This is the same habit of checking row counts from earlier chapters, now applied one layer at a time.

with paid_orders as (
  select *
  from orders
  where status in ('paid', 'shipped')
)
select count(*) as paid_order_rows
from paid_orders;

Scalar just means a single value. A scalar subquery returns exactly one row and one column — one value — so you can use it anywhere SQL expects a single value, most often in a WHERE comparison.

It is the natural way to compare every row against one number worked out from the whole table: above the overall average, after the latest timestamp, bigger than the company-wide total.

The subquery runs once to work out that one number, then every row from the main query is compared against it.

scalar subquery

A subquery that returns a single value — one row, one column. “Scalar” is just the technical word for “a single value”.

select
  order_id,
  total_amount
from orders
where total_amount > (
  select avg(total_amount)
  from orders
)
order by total_amount desc, order_id;

A correlated subquery looks at a column from the row the main query is currently checking. Because that row keeps changing, the subquery runs again for every one of those rows. EXISTS is the most common form: “is there at least one matching row?”

In the first example below, one line does all of this: `where o.buyer_id = c.customer_id`. The `c` belongs to the outer query, so that line reaches out to the customer being checked right now. That reach is the correlation, and it is why the inner query cannot run just once — it has to run again for each customer. Compare that with the scalar subquery from the previous section, which runs once and hands the same number to every row.

Unlike a join, EXISTS returns each row from the main query at most once, so it never duplicates rows — the same fan-out-safe pattern from Chapter 2. (Fan-out is when a join accidentally makes copies of your rows.)

Use it for eligibility and lifecycle questions: customers who have ordered, accounts with a failed payment, users with any activity this week.

select
  c.customer_id,
  c.country
from customers as c
where exists (
  select 1
  from orders as o
  where o.buyer_id = c.customer_id
    and o.status = 'shipped'
)
order by c.customer_id;

-- drop the status filter: now buyer 1 matches three times
select
  c.customer_id,
  c.country
from customers as c
where exists (
  select 1
  from orders as o
  where o.buyer_id = c.customer_id
)
order by c.customer_id;

A derived table is a subquery in the FROM clause that the outer query treats like a table. It is handy when you need to aggregate first and then filter on that aggregate.

It is closely related to a CTE: both give a name to a middle step. A derived table is short and stays where it is used; a CTE is named up top, can be reused, and is easier to check on its own.

A common use is the “aggregate then filter” shape: compute buyer revenue, then keep only the high-value buyers.

select
  buyer_id,
  revenue
from (
  select buyer_id, sum(total_amount) as revenue
  from orders
  group by buyer_id
) as buyer_totals
where revenue >= 200
order by revenue desc, buyer_id;

Open a messy query and you will often spot the same small fix written again and again — a COALESCE here, a CASE there, all doing the exact same job.

Every copy is a chance to get it wrong. If the rule ever changes, you have to hunt down and update all of them. Miss one, and that copy keeps giving a different answer with nothing to warn you.

The fix is to do the cleaning once, in a CTE near the top, and give the cleaned column a name. Every layer below it just uses that name and never repeats the fix.

-- coalesce(c.country, 'unknown') appears three times
select
  coalesce(c.country, 'unknown') as country,
  count(*)                       as orders,
  sum(o.total_amount)            as revenue
from orders as o
join customers as c
  on c.customer_id = o.buyer_id
where coalesce(c.country, 'unknown') <> 'IN'
group by coalesce(c.country, 'unknown')
order by revenue desc;

with cleaned_customers as (
  select
    customer_id,
    coalesce(country, 'unknown') as country
  from customers
)
select
  c.country,
  count(*)            as orders,
  sum(o.total_amount) as revenue
from orders as o
join cleaned_customers as c
  on c.customer_id = o.buyer_id
where c.country <> 'IN'
group by c.country
order by revenue desc;

Some questions cannot be answered with a fixed number of joins, because the answer depends on how deep the data goes. "Everyone beneath this manager" is the classic: one join gets the direct reports, two gets their reports, and the tree keeps going.

A recursive CTE has two halves joined by UNION ALL. The anchor produces the starting rows. The recursive term joins the table back to the rows the CTE has produced so far, and runs again on whatever it produced — until an iteration produces nothing.

The word RECURSIVE goes after WITH, once, even when several CTEs follow: `with recursive x as (...), y as (...)`. In SQL Server it is omitted entirely, which is the only dialect difference worth memorising here.

with recursive subtree as (
  -- anchor: the direct reports
  select employee_id, full_name, 1 as depth
  from employees
  where manager_id = 2

  union all

  -- recursive term: the reports of everyone we have already found
  select e.employee_id, e.full_name, s.depth + 1
  from employees as e
  join subtree as s on e.manager_id = s.employee_id
)
select employee_id, full_name, depth
from subtree
order by depth, employee_id;

with recursive chain as (
  select employee_id, full_name, full_name as path, 1 as depth
  from employees
  where manager_id is null          -- the root: IS NULL, never = NULL

  union all

  select e.employee_id, e.full_name,
         c.path || ' > ' || e.full_name,
         c.depth + 1
  from employees as e
  join chain as c on e.manager_id = c.employee_id
)
select full_name, depth, path
from chain
where depth >= 4
order by employee_id;

with recursive subtree as (
  select employee_id, full_name, 0 as levels_down
  from employees
  where employee_id = 2

  union all

  select e.employee_id, e.full_name, s.levels_down + 1
  from employees as e
  join subtree as s on e.manager_id = s.employee_id
  where s.levels_down < 2          -- the bound belongs HERE
)
select full_name, levels_down
from subtree
where levels_down > 0
order by levels_down, employee_id;

Unnamed subqueries tucked inside other unnamed subqueries are the classic “write-only” SQL — easy to write, painful to read. It runs, but nobody, including you next month, can quickly say what each layer means.

The query below works, but the inner aggregate has no name, so you cannot easily say what one of its rows stands for, or run it on its own to check it.

The fix is almost always the same: pull the nested subquery out into a named CTE, so each layer can be read and run by itself.

-- the inner aggregate is anonymous: what is its grain?
select buyer_id, revenue
from (
  select buyer_id, sum(total_amount) as revenue
  from orders
  where status = 'paid'
  group by buyer_id
) as x
where revenue > 100
order by revenue desc, buyer_id;

A layered query has CTEs doing the work at the top, and then one last SELECT at the bottom that produces the answer. That last one is the final SELECT.

It should do almost nothing. No SUM, no CASE, no filters, no math. It just picks the columns, puts them in order, and hands them over. All the real thinking already happened in the layers above it.

Think of maths homework. You show your working line by line down the page, and the last line is simply “= 42”. You would never cram the whole calculation into that last line. Your final SELECT is the “= 42” line.

So “boring” is a compliment here. A boring final SELECT is a sign that the layers above it did their jobs.

with buyer_revenue as (
  select buyer_id, sum(total_amount) as revenue
  from orders
  group by buyer_id
)
select
  buyer_id,
  revenue
from buyer_revenue
order by revenue desc, buyer_id;

PostgreSQL, BigQuery, Snowflake, and SQLite all support WITH queries. What differs is how each one runs a CTE behind the scenes: some work out its rows once and store them for the rest of the query (the technical word is materialising), while others fold the CTE into the main query and run it as one piece.

A clean CTE stack makes a query easier to read and check. It does not automatically make it faster. Think of CTEs as a way to organise, not to speed things up.

Once the query is correct and easy to explain, and only if the table is large, look at the query plan — the database’s own description of how it intends to run your query — and adjust if a CTE is being handled in a way that hurts.

with recent_orders as (
  select *
  from orders
  where created_at >= '2026-01-01'
)
select count(*) as recent_orders
from recent_orders;

These six problems turn vague business questions into named, layered queries, and each opens in the live SQL workspace on the same marketplace dataset — customers, orders, and order_items.

Treat each as a small interview: state the row-set story (which rows, which grain, which metric), write the layers, run it to inspect the output, then submit for scored feedback.

with paid_orders as (
  select order_id, buyer_id, total_amount
  from orders
  where status in ('paid', 'shipped')
)
select *
from paid_orders
order by order_id;