Correlated Subqueries and types of subqueries , in SQL #sql

A correlated subquery is an inner query that references a column from the outer query's current row — so it can't run once and be done; it has to re-run for every single outer row, using that row's specific value. The mechanics, step by step: Take the next row from the outer table. Substitute that row's value(s) into the inner query wherever it references the outer alias (e.g. e1.dept → 'Sales'). Run the now-fully-resolved inner query on its own. Drop that result back into the outer WHERE/SELECT in place of the subquery. Evaluate the condition — keep the row if true, discard if false. Repeat for the next row. This is different from an independent (uncorrelated) subquery, which never references the outer table, runs exactly once, and produces one fixed value every outer row gets compared against. The four examples in the page, in order of difficulty: Scalar in SELECT — a correlated subquery can just compute and attach a value (e.g. department average) to every row without filtering anything. Filter in WHERE — that same average now gates which rows survive (salary 'greater sign' dept avg). EXISTS — checks only whether the inner query finds any row at all (e.g. "does this customer have an order over $500"), ignoring what the row actually contains. 2nd-highest salary per department (the hard one) — uses a correlated COUNT instead of an aggregate comparison, and exposes a real gotcha: when two people tie for the top salary in a department, neither has exactly one person above them, so the department silently produces no result at all. Key pitfalls it calls out: Ties can make ranking-style correlated subqueries return nothing, with no error. They re-run per outer row, so large tables can get slow — this is why query planners often try to rewrite them as joins. NOT EXISTS handles NULLs safely; NOT IN against a list containing NULL can silently break. #coding #subqueries #subquery #mysql