Joins

Joins combine rows from related tables on a predicate — usually equality on keys. The join type controls which rows survive when matches or nulls appear on either side.

| Join | Keeps rows when | |------|-----------------| | `INNER JOIN` | Match exists on both sides | | `LEFT JOIN` | All left rows; null-extended right if no match | | `RIGHT JOIN` | Mirror of left — rare in practice | | `FULL OUTER` | All from both; nulls where no match | | Semi (`EXISTS`, `IN`) | Left rows with at least one match — no duplicate expansion | | Anti (`NOT EXISTS`) | Left rows with no match |

					-- Users with at least one order (semi-join shape)
SELECT u.id, u.email
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Users who never ordered (anti-join)
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
				

Cardinality matters: joining one-to-many without aggregation duplicates parent facts — wrap in a subquery or use `DISTINCT ON` / window functions deliberately. Filter early; push predicates that reduce join width before the join when the planner allows.

On interviews: given a report requirement, pick inner vs left vs exists and explain duplicate risk and null handling.

Common pitfalls: implicit comma joins without clear predicates; `LEFT JOIN` then `WHERE right.col = X` (turns into inner); `OR` join conditions that block index use; joining wide tables before filtering.

The trade-off is balancing readable SQL against plan cost — semi/anti joins often beat `DISTINCT` after an inner join, but the readable shape depends on engine and statistics.

Checklist:

• State cardinality (1:1, 1:N, N:M) for the join.
• Pick join type from null and duplicate requirements.
• Prefer `EXISTS` over `IN` for correlated semi-joins at scale.
• Filter driving table rows before the join when possible.
• Mention index needs on join keys.