Artful Common Queries

Correlated aggregating subqueries

from the Artful Common Queries page

If you need a child table aggregate result for each row in a query, what's faster, putting the correlated aggregating subquery in the SELECT list, or in the FROM clause? Thus, given tables stocks(id, user, ticker,lastvalue) and transactions(id, sid, action, qty, price, commission), and the requirement to retrieve stock values and per-stock transaction counts, which of these queries will run faster?

select 
  ticker, id, lastvalue,
  (select count(*) 
from transactions 
where sid=stocks.id) N 
from stocks;

... or ...

select s.ticker, s.id, s.lastvalue, t.N
from stocks s 
join (
  select sid, count(*) N 
  from transactions
  group by sid
) t on s.id=t.sid;

The first query's syntax is simpler, so it's often the first choice. EXPLAIN reports that the second query requires examination of more rows than the first query does. But benchmarks with caching turned off show that the second query is much faster--mainly because it executes one subquery rather than a subquery per row. If you have a choice, put the aggregating subquery in the FROM clause.

Last updated 27 Jan 2020