Sql - EXISTS and correlated subqueries
Code Life
By Pierre Marsaa
A subquery that looks outside
So far the subquery was independent: you could run it on its own.
A correlated subquery refers to the current row of the outer query — here t.trainer_id — so it is evaluated once per row:
↗️
Loading editor...
↶
↷
For Alex the subquery counts the badges with trainer_id = 1, for Morgan = 2, and so on. Jordan gets 0, not NULL — and there is no GROUP BY and no LEFT JOIN.
Correlated = depends on the outer row
You cannot run SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = t.trainer_id alone: t does not exist outside the big query. That is the sign of a correlated subquery.
Also in UPDATE
Subqueries are not only for SELECT. UPDATE trainers SET badges = (SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = trainers.trainer_id); recomputes the badges column of every trainer from the real badge rows — one correlated subquery, one statement.
Verify