Study notes · 12% of the exam

Joins

Know exactly which rows each join type keeps, how duplicates multiply, and where NULLs and filter placement silently change results.

Key points

1
INNER keeps matching pairs; LEFT/RIGHT keep every row of the preserved side, NULL-padded; FULL keeps both; CROSS returns every combination.
2
Join conditions use three-valued logic: NULL never equals anything, so NULL keys never match (use IS NOT DISTINCT FROM if you truly need it).
3
Duplicate keys multiply: m matches × n matches per key. Joining a parent to two child tables multiplies them, which inflates SUM and COUNT.
4
A filter on the right table belongs in ON to keep unmatched rows; the same filter in WHERE turns a LEFT JOIN into an inner join.
5
Anti-joins: use NOT EXISTS or LEFT JOIN … WHERE right.pk IS NULL. NOT IN returns nothing if the subquery yields a NULL.
6
LATERAL lets a subquery or set-returning function reference earlier tables; use LEFT JOIN LATERAL … ON true to keep rows with no results.
7
Hash and merge joins need an equality condition; range joins run as nested loops and need an index on the inner side. Index foreign key columns yourself.

COUNT(*) after a LEFT JOIN counts the NULL-padded row as 1; use COUNT(right.pk).
BETWEEN is inclusive at both ends, so range bands that share a boundary match twice; use half-open ranges.
NATURAL JOIN joins on every shared column name, so adding a column such as updated_at to both tables changes the results.

Ten questions, with the answer and explanation after each one.