SQL essentials
Order of execution, joins, aggregation, windows. 12 cards. Click a card to flip, or use the arrow keys and space bar.
Logical processing order of a SELECT?
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
Why can WHERE not use a SELECT alias?
WHERE is evaluated before SELECT, so the alias does not exist yet. ORDER BY runs after, so it can.
WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation.
COUNT(*) vs COUNT(col)?
COUNT(*) counts rows; COUNT(col) counts non-null values of that column.
Why does NOT IN sometimes return zero rows?
A NULL in the value list makes every comparison unknown. Use NOT EXISTS.
LEFT JOIN plus WHERE on the right table = ?
Effectively an INNER JOIN — the NULL-extended rows fail the filter. Put the condition in ON.
RANK vs DENSE_RANK vs ROW_NUMBER on ties?
RANK repeats then skips (1,2,2,4); DENSE_RANK repeats without skipping (1,2,2,3); ROW_NUMBER never ties.
Why wrap a window function in a subquery to filter it?
Windows are evaluated after WHERE, so filtering must happen one level up.
What does ORDER BY inside OVER() change?
It changes the default frame, turning an aggregate into a running total.
UNION vs UNION ALL?
UNION removes duplicates and costs a sort/hash; UNION ALL keeps everything and is faster.
What prevents an index seek on a filtered column?
Wrapping it in a function, a leading wildcard LIKE, or an implicit type cast.
Composite index on (a,b) — what can it serve?
Predicates on a, or a and b together. Not b alone — prefix order matters.