SQL Fundamentals
SELECT, WHERE, and JOIN — retrieving and combining rows from relational tables.
Try answering in your head first, then click a question to check the model answer.
Q1.Why can't you filter on an aggregate function (like `COUNT(*)`) using `WHERE`?(show answer)
WHERE is evaluated before rows are grouped and aggregated, so aggregate values don't exist yet at that stage. HAVING runs after GROUP BY, once aggregates are computed, so it's the correct clause for filtering on them — e.g. HAVING COUNT(*) > 5.
Q2.Explain the difference between LEFT JOIN and INNER JOIN with an example.(show answer)
INNER JOIN returns only rows where the join condition matches in both tables — e.g. joining orders to customers returns only orders with a valid customer. LEFT JOIN returns every row from the left table regardless of a match, filling in NULLs for the right table's columns when there's no match — useful for finding orders even if the customer record was deleted, or explicitly finding unmatched rows via WHERE right.id IS NULL.
Q3.Why does SQL's logical evaluation order (FROM before SELECT) matter in practice?(show answer)
It explains why you can't reference a SELECT-defined column alias in the same query's WHERE clause (in most databases) — WHERE is evaluated before SELECT computes the alias. It's also why GROUP BY/HAVING can reference raw columns from FROM/JOIN that haven't been projected by SELECT yet.
Q4.When would you use `UNION ALL` instead of `UNION`?(show answer)
Whenever you don't need duplicates removed, since UNION ALL skips the deduplication step and is therefore faster — often you already know the two result sets are disjoint (e.g. combining data from two non-overlapping date ranges), making UNION's extra dedup work pure waste.