What's the difference between `INNER JOIN` and `LEFT JOIN`?
`INNER JOIN` returns only rows that match in both tables. `LEFT JOIN` returns every row from the left table, with `NULL` filled in for any missing right-side match.
02
When do you use `GROUP BY`?
When you want to aggregate rows that share a value into summary rows — typically with functions like `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`.
03
What does `HAVING` do that `WHERE` doesn't?
`WHERE` filters rows before grouping. `HAVING` filters groups after `GROUP BY`. You need `HAVING` to filter on aggregate results like `COUNT(*) > 5`.
04
What is a primary key?
A column (or set of columns) that uniquely identifies each row in a table. It's `NOT NULL` and indexed automatically.
05
What is a foreign key?
A column that references the primary key of another table, enforcing referential integrity between tables.
06
What does `UNION` vs `UNION ALL` do?
Both stack the results of two `SELECT`s vertically. `UNION` removes duplicate rows; `UNION ALL` keeps everything (faster because no dedup pass).
07
When would you use a subquery vs a JOIN?
Use a JOIN when you need columns from both tables in the result. Use a subquery when you only need to filter or compute against another table without including its columns.
08
What is an index, and what's the trade-off?
A data structure (usually a B-tree) that speeds up reads on the indexed columns. The trade-off: every write must update the index too, slowing inserts and increasing storage.
09
What does `LEFT JOIN ... WHERE right.col IS NULL` find?
Rows from the left table that have no matching row in the right table — a classic 'anti-join' to find orphans or missing data.
10
What's a window function?
A function (like `ROW_NUMBER()`, `RANK()`, `SUM() OVER (...)`) that computes a value across a set of rows related to the current row, without collapsing them like `GROUP BY` does.