Lesson 2 of 8 · 48 min

Joins & nulls under interview pressure

INNER/LEFT/FULL/CROSS, semi and anti joins, ON vs WHERE, null keys, chasm traps, and existence patterns that avoid fan-out.

Lesson 2 · Joins & nulls

Every join type with the traps that fail interviews

Join posters are not enough

Candidates can draw Venn diagrams and still ship wrong business results. Interviews grade which rows survive, how NULL keys never match, when a LEFT JOIN becomes INNER via WHERE, and how semi/anti joins express existence without fan-out. This lesson is pure join mechanics with runnable patterns.

Start every join answer with: left grain, right grain, relationship (1:1, 1:N, N:M), and whether unmatched rows must appear. Then pick INNER / LEFT / FULL / CROSS / semi / anti. Venn diagrams help intuition for INNER/LEFT/FULL but fail for semi-joins and for multi-key matches.

INNER, LEFT, RIGHT, FULL, CROSS

INNER keeps matched pairs only. LEFT keeps all left rows, null-pads right. RIGHT is LEFT with sides flipped (often rewritten as LEFT for style). FULL OUTER keeps both sides' unmatched rows. CROSS is cartesian product - useful for spine dates × entities, dangerous when accidental.

sql

1-- Setup mental tables2-- users:  (1 Alice), (2 Bob), (3 Cara)3-- orders: (10, user 1), (11, user 1), (12, user 2)4-- user 3 has no orders; no order references missing user 9956SELECT u.user_id, o.order_id7FROM users u8INNER JOIN orders o ON o.user_id = u.user_id;9-- rows: (1,10),(1,11),(2,12)  - Cara gone1011SELECT u.user_id, o.order_id12FROM users u13LEFT JOIN orders o ON o.user_id = u.user_id;14-- rows: above + (3, NULL)1516SELECT u.user_id, o.order_id17FROM users u18FULL OUTER JOIN orders o ON o.user_id = u.user_id;19-- same as left here if referential integrity holds

sql

1-- Trap: filter in WHERE collapses LEFT to INNER2SELECT u.user_id, o.order_id, o.status3FROM users u4LEFT JOIN orders o ON o.user_id = u.user_id5WHERE o.status = 'paid';   -- drops users with no paid orders AND users with no orders67-- Keep all users; attach paid orders only8SELECT u.user_id, o.order_id, o.status9FROM users u10LEFT JOIN orders o11  ON o.user_id = u.user_id12 AND o.status = 'paid';

Semi-joins and anti-joins (existence without fan-out)

Semi-join: keep left rows that have at least one match, without multiplying left rows. Anti-join: keep left rows with no match. Prefer EXISTS / NOT EXISTS for null-safety and clarity. IN is fine for small non-null lists; NOT IN is the footgun from L1.

sql

1-- Semi-join: users who have at least one order (no duplicate users)2SELECT u.*3FROM users u4WHERE EXISTS (5  SELECT 1 FROM orders o WHERE o.user_id = u.user_id6);78-- Equivalent-ish INNER JOIN + DISTINCT/GROUP - but EXISTS states intent better9SELECT DISTINCT u.*10FROM users u11JOIN orders o ON o.user_id = u.user_id;1213-- Anti-join: users with zero orders14SELECT u.*15FROM users u16WHERE NOT EXISTS (17  SELECT 1 FROM orders o WHERE o.user_id = u.user_id18);1920SELECT u.*21FROM users u22LEFT JOIN orders o ON o.user_id = u.user_id23WHERE o.user_id IS NULL;

Why EXISTS beats JOIN+DISTINCT for existence: you cannot accidentally SUM a multiplied measure, the intent is readable, and planners often short-circuit on first match. Use joins when you need columns from both sides or you are building a wider fact row.

Self-joins and hierarchical pairs

Self-joins compare a table to itself: employee→manager, flight→connection, event→previous event (though windows often beat self-joins for 'previous row'). Always alias clearly (e/m, o1/o2) and state the match key plus directionality.

sql

1-- Employees with manager name2SELECT e.employee_id, e.name AS employee, m.name AS manager3FROM employees e4LEFT JOIN employees m ON m.employee_id = e.manager_id;56-- Pairs of orders by same user within 24h (self-join explosion risk)7SELECT o1.order_id AS first_id, o2.order_id AS second_id8FROM orders o19JOIN orders o210  ON o2.user_id = o1.user_id11 AND o2.order_id > o1.order_id12 AND o2.created_at <= o1.created_at + INTERVAL '24 hours';13-- Prefer windows/lags when 'next event' is the real question

Multiple joins and bridge tables (N:M)

Many-to-many needs a bridge: user_roles, post_tags, order_coupons. Joining both ends without aggregating produces combinatorial explosion. Interview move: decide if the grain is user-role pairs (keep explosion) or users with role counts (aggregate bridge first).

sql

1-- N:M without care2SELECT u.user_id, r.role_name3FROM users u4JOIN user_roles ur ON ur.user_id = u.user_id5JOIN roles r ON r.role_id = ur.role_id;6-- grain: one row per user-role assignment - OK if intended78-- Wrong: sum user spend after exploding roles9SELECT u.user_id, SUM(o.amount)  -- inflated by number of roles10FROM users u11JOIN user_roles ur ON ur.user_id = u.user_id12JOIN orders o ON o.user_id = u.user_id13GROUP BY u.user_id;1415-- Fix: aggregate facts at user grain first16WITH spend AS (17  SELECT user_id, SUM(amount) AS revenue FROM orders GROUP BY user_id18)19SELECT u.user_id, s.revenue, COUNT(ur.role_id) AS n_roles20FROM users u21LEFT JOIN spend s ON s.user_id = u.user_id22LEFT JOIN user_roles ur ON ur.user_id = u.user_id23GROUP BY u.user_id, s.revenue;

Nullable join keys and IS NOT DISTINCT FROM

Business keys sometimes null (external_id not yet assigned). Standard = does not match nulls. PostgreSQL offers IS NOT DISTINCT FROM for null-safe equality; elsewhere use (a = b OR (a IS NULL AND b IS NULL)). Know it exists; use sparingly and document meaning.

sql

1-- Null-safe equality (Postgres)2SELECT *3FROM a4JOIN b ON a.external_id IS NOT DISTINCT FROM b.external_id;56-- Portable pattern7ON a.external_id = b.external_id8OR (a.external_id IS NULL AND b.external_id IS NULL)

Interview drills - speak the row set

01"INNER vs LEFT?" → INNER only matches; LEFT keeps all left rows with null-padded right when no match.
02"When FULL OUTER?" → reconcile two sources both may miss keys (billing vs CRM); rare in app OLTP.
03"EXISTS vs IN?" → EXISTS is null-safe and short-circuit friendly; IN is fine for non-null small sets.
04"NOT IN vs NOT EXISTS?" → NOT EXISTS/anti-join; NOT IN breaks if subquery yields NULL.
05"Why did LEFT become INNER?" → WHERE filtered right-side columns, dropping null-extended rows.
06"Semi-join meaning?" → filter left by existence of match without duplicating left rows.
07"Chasm trap?" → two 1:N joins off one parent multiply rows; aggregate facts separately first.
08"NULL join keys?" → never match under =; use null-safe equality only with explicit product meaning.
09"CROSS JOIN use?" → date spine × accounts, parameter grids - never accidental.
10"Self-join vs window?" → self-join for graph-ish pairs; LAG/LEAD for ordered previous/next attributes.

SQL Joins Explained (with examples)ByteByteGo / common join explainers - verify ID when embedding docsPostgreSQL joins documentationPostgreSQL docsUse The Index, Luke - join performanceUse The Index, Luke articleMode - SQL joins explanationMode docsClickHouse / warehouse notes on join types (optional depth)ClickHouse

Interview habit: before you write a join or window, say the grain of the result set out loud (one row per what?), name the filters that apply before aggregation, and sketch a 4-row toy table. That 30-second ritual catches double-counting and null traps faster than staring at syntax.

When stuck live, reduce to two tables and three rows on the whiteboard. Prove the join, then reintroduce filters and aggregates. Interviewers credit recovery more than a first-draft perfect query that you cannot explain.

State timezone and late-arriving data assumptions once. 'UTC calendar day of paid_at; events may land +2h' prevents half the metric arguments in marketplace and consumer analytics rounds.

Prefer COUNT(DISTINCT key) or EXISTS for existence metrics; prefer SUM of a measure that already lives at the target grain. If you must join a child table, aggregate the child in a CTE first and say why.

Read the English noun in the question: users, orders, sessions, days. That noun is usually the GROUP BY or partition key. If your SELECT invents a different noun, you probably changed the grain by accident.

Null policy belongs in the spoken answer: 'missing country is excluded from geo breakouts but included in totals' is a product decision. Silent COALESCE to 'unknown' without saying so is how dashboards disagree.

Checkpoint

List all users and the count of 'paid' orders only, including users with zero paid orders. Best pattern?

AFROM users INNER JOIN orders ON user_id WHERE status='paid' GROUP BY userBFROM users LEFT JOIN orders ON user_id AND status='paid', then COUNT(o.order_id) GROUP BY userCLEFT JOIN all orders then WHERE status='paid' before GROUP BY

Checkpoint

You need users who never placed an order. Which is safest under possible NULL user_id in orders?

AWHERE user_id NOT IN (SELECT user_id FROM orders)BWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.user_id)CINNER JOIN orders then filter users with COUNT = 0

Checkpoint

Joining users → orders (1:N) and users → sessions (1:N) in one query then SUM(order.amount) and COUNT(sessions) - risk?

ANo risk if both FKs are indexedBChasm/fan-out: each order row multiplies with each session row, inflating SUM and COUNTCOnly COUNT is wrong; SUM is immune to row multiplication

Checkpoint

When do two rows with NULL in the join column match under ON a.key = b.key?

AAlways - SQL treats nulls as equal for join purposesBNever under standard equality; null keys do not match each otherCOnly on LEFT JOIN; INNER JOIN matches nulls

Checkpoint

Pick the best expression for 'customers who bought SKU 9' without duplicating customer rows for multi-quantity lines.

ASELECT c.* FROM customers c JOIN order_items i ON ... WHERE i.sku=9BSELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM order_items i JOIN orders o ON ... WHERE o.customer_id=c.id AND i.sku=9)CSELECT DISTINCT ON (sku) customer rows from a cross join of items

Can you choose INNER/LEFT/EXISTS/NOT EXISTS and place ON vs WHERE filters without turning left joins into inners?

New to itGetting thereConfident

Takeaways

Name relationship and unmatched-row policy before typing JOIN.
ON filters preserve left rows; WHERE on right cols often does not.
EXISTS/NOT EXISTS for semi/anti; avoid NOT IN with nulls.
Two 1:N joins off one parent = chasm trap; aggregate separately.
NULL keys never match under =.

Next: aggregations, GROUP BY discipline, and the double-count patterns interviewers plant on purpose.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free