⬅️LEFT JOIN

Keep every left row; pad the right with NULL

LEFT OUTER JOIN (usually written LEFT JOIN) starts from the preserved “left” operand: for each left row, the engine attaches matching right rows. If none match, right-side columns appear as NULL. Result row count is at least the left input row count (before WHERE), unless you filter those NULL-extended rows away.

This is the right tool for optional relationships: every study site on a roster, with enrollment counts if enrollments exist; every freezer slot, with current box id if occupied; every API user, with last login time if they have ever authenticated.

SQL

Anti-join: rows with no partner

SELECT p.patient_id, p.mrn, f.next_visit
FROM patients p
LEFT JOIN followups f ON f.patient_id = p.patient_id
WHERE f.next_visit IS NULL;

✓Illustrative result set

patient_id	mrn	next_visit
1201	MRN-0001201	NULL
1204	MRN-0001204	NULL

LEFT JOIN then WHERE on a right column (non-NULL test)

LEFT JOIN offers o … WHERE o.season = 'SPRING' removes catalog rows whose offer is missing or a different season, because WHERE runs after the join and drops NULL-extended rows.

Move that predicate into ON when it defines the match

Write LEFT JOIN offers o ON … AND o.season = 'SPRING' if you still need all catalog rows, with NULL offer columns when no spring offer exists. Keep global exclusions in WHERE.

SQL

Count children per parent (LEFT + GROUP BY)

SELECT s.study_id, COUNT(e.subject_id) AS enrolled
FROM studies s
LEFT JOIN enrollments e ON e.study_id = s.study_id
GROUP BY s.study_id;

✓Illustrative result set

study_id	enrolled
ST-1	0
ST-2	144

🔢COUNT(*) vs COUNT(child_col) after LEFT JOIN

Both appear in reporting SQL; they answer different questions.

COUNT(*) counts result rows per group, if the right side is not unique, parents duplicate and the count inflates. Fix uniqueness or aggregate children first.
COUNT(e.subject_id) counts non-NULL child keys, often “number of enrollments” when each enrollment is one row. For “distinct subjects enrolled,” use COUNT(DISTINCT e.subject_id).

SQL

Optional enrichment: correlated subquery (portable)

SELECT d.device_id,
       (SELECT MAX(h.seen_at)
        FROM device_heartbeats h
        WHERE h.device_id = d.device_id) AS last_seen
FROM devices d;

✓Illustrative result set

device_id	last_seen
DEV-01	2026-04-12 08:00
DEV-99	NULL

⚡ Advanced / dialect note

ORMs sometimes emit huge LEFT chains, each hop can duplicate rows if an earlier hop was already one-to-many. Materialize a stable “base grain” CTE, then join dimensions to that CTE so cardinality stays obvious.

Do not paper over bad FKs

If INNER was correct for integrity but you switched to LEFT because tests failed, you may be hiding missing parents instead of fixing ingestion. LEFT is for optional semantics, not for silencing constraint violations.

TL;DR

LEFT OUTER JOIN keeps all rows from the left table, pairing with right rows when the predicate matches and filling right columns with NULL otherwise, modeling optional relationships (e.g. patient without a survey). Filters on the right side usually belong in the ON clause (or a subquery) if you need to preserve left rows; moving them to WHERE can accidentally turn the join inner.