Key idea
Count pairs, not assumptions
An inner join returns matching row pairs. A one-to-many relationship repeats the “one” side once per match, while an unmatched row disappears. Predict those cardinalities before adding another join or aggregate.
Unit cheatsheet
Key ideas collected from the lessons in this unit.
Key idea
An inner join returns matching row pairs. A one-to-many relationship repeats the “one” side once per match, while an unmatched row disappears. Predict those cardinalities before adding another join or aggregate.
Key idea
Use ON to define which right rows match. Use WHERE to filter the joined result. For an
anti-join, check a non-nullable right key with IS NULL; for an optional right-side filter that
must preserve unmatched left rows, keep that filter in ON.
Key idea
count(*) counts rows. count(column) skips rows where that column is NULL. GROUP BY creates
one output row per group; HAVING chooses which groups survive.
Key idea
Use EXISTS to test for at least one match and NOT EXISTS to test for none. Reserve NOT IN
for cases where the left and right values are known non-NULL, or explicitly remove NULLs from the
right-hand set.
Key idea
Use UNION when the combined result should contain unique rows. Use UNION ALL when every input
row should remain. INTERSECT and EXCEPT describe overlap and difference between result sets.
Key idea
Store event instants as timestamptz. Convert to the named zone whose calendar you are reporting
before deriving a local date or bucket. Keep the zone name separately when future local wall time
is part of the business requirement.