A subquery is a query nested inside another. Where it appears determines what it must return: Location Returns Example WHERE x = (…) single value (scalar) salary above the company average WHERE x IN (…) a list customers who ordered WHERE EXISTS (…) true/false at least one matching row FROM (…) AS t a derived table aggregate, then join SELECT (…) scalar per row correlated lookup Correlated subqueries A subquery that references the outer query runs once per outer row — e.g. "employees earning above their own department's average". Often rewritable as a JOIN or window function for speed. EXISTS vs IN EXISTS stops at the first match and handles NULLs safely. Beware NOT IN: if the subquery returns any NULL, NOT IN returns no rows — prefer NOT EXISTS.
Key Takeaways
Subqueries can return a value, a list, a table or a boolean (EXISTS).
Correlated subqueries run per outer row — consider JOINs or window functions.
Prefer NOT EXISTS over NOT IN when NULLs are possible.
Derived tables let you aggregate first, then join or filter.