Lessons · SQL · a subquery that runs per row
A subquery that runs per row
A correlated subquery references the outer row (o.customer_id), so it is evaluated for each outer row. It answers per-group questions, and it costs a query per row.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Above their own average, more than their previous order, the first of their kind: comparisons against the row's own group. Correct and clear at small scale; slow at large scale unless rewritten as a join or a window.
How to think about it
Write it correlated first, because it reads like the question. If it is slow, compute the group statistic once with GROUP BY or a window and join it back.
Worked example
SELECT o.id FROM orders o WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id);Orders above their own customer's average: the inner query runs once per outer row.
SELECT o.id 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;The same answer with the averages computed once, then joined.
Your turn
Reviews above their film's average stars.
SELECT r.id FROM reviews r WHERE r.stars > (SELECT AVG(r2.stars) FROM reviews r2 WHERE r2.film_id = r.);
Run a query against real tables
The trap
Forgetting the correlation (o2.customer_id = o.customer_id) compares every row against the global average and looks plausible. Check the inner WHERE mentions the outer alias.