Subqueries

A query that returns one value

A scalar subquery is a value with a query where the number should be. It can go anywhere a value can: in where, in select, in an expression. If it returns more than one row you get an error — which is a useful kind of strictness.

the average is computed once, then compared against every row

select name, price
from   doors
where  price > (select avg(price) from doors)      -- 1734.05
order by price desc;

-- Noir 4200 · Nyctor 3100 · Arclith 2400 · Caivor 1980

A query that returns a list

in, with rows instead of literals

select name from customers
where  id in (select customer_id from orders where status = 'cancelled');

not in plus one NULL returns nothing

The NULL lesson's sharpest edge lives here. If the subquery can produce a NULL — any nullable column — not in silently returns an empty result. Add where col is not null, or use not exists.

exists: a yes/no question about rows

exists stops at the first matching row — it never has to count or collect anything. select 1 is convention: the column list is ignored, so there is no point writing one.

The inner query mentions c.id from the outer query, which makes it correlated: conceptually it runs once per candidate row. Planners are good at turning that into a single pass, but the mental model is the loop.

the seven customers who have bought a pivot door

select c.name
from   customers c
where  exists (
  select 1
  from   orders o
  join   order_items i on i.order_id = o.id
  join   doors d       on d.id = i.door_id
  where  o.customer_id = c.id            -- the correlation
    and  d.style = 'pivot'
)
order by c.name;

exists versus join, for the same question

When the question is "does at least one exist?", reach for exists. When you need columns from the other table, join. A distinct sitting on top of a join is often a sign that exists was the right tool.

a join can duplicate; exists cannot

-- three pivot lines for one customer → that customer appears three times
select distinct c.name
from   customers c
join   orders o      on o.customer_id = c.id
join   order_items i on i.order_id = o.id
join   doors d       on d.id = i.door_id and d.style = 'pivot';

-- no duplicates to remove in the first place
select c.name from customers c where exists (…);

A subquery in the select list

Readable, and fine for a handful of rows. Over a large table it is one query per row unless the planner rewrites it — the same result comes from a left join to a grouped subquery, which is one pass.

one value per row, computed on the side

select c.name,
       (select max(o.placed) from orders o where o.customer_id = c.id) as last_order
from   customers c
order by last_order desc;

A subquery as a table

You cannot average an average directly — you have to build the per-order totals first, then average those. A derived table needs an alias in most databases (t here). When the query grows past two of these, the next lesson's with says the same thing top to bottom instead of inside out.

a derived table: group first, then ask about the groups

select round(avg(total), 2) as average_order        -- 5799.35
from (
  select order_id, sum(qty * unit_price) as total
  from   order_items
  group by order_id
) t;