LEFT JOIN and the Orphan Finder

~12 min · queries, joins

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Keeping the parent regardless

LEFT JOIN is INNER JOIN's gentler cousin: every row from the left table is preserved, even when there's no matching row on the right. Unmatched rows get NULLs in the right-side columns.

The orphan-finder pattern

LEFT JOIN ... WHERE right.id IS NULL finds parents with no children: users who never ordered, posts with no comments, products never reviewed. This pattern shows up in nearly every analytics dashboard.

The ON-vs-WHERE trap

Filters on the right table belong in ON if you want LEFT JOIN to keep its semantics. Putting WHERE o.total > 100 after a LEFT JOIN silently drops rows where there's no order at all (NULL fails the comparison) — turning it back into an INNER JOIN.

Code

Every user, with orders if any·sql

SELECT u.name, o.id AS order_id, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER  BY u.name, o.placed_at;
-- Carol, who has no orders, still appears with NULL columns on the right.

Find orphans·sql

-- Users who have never ordered
SELECT u.id, u.name
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  o.id IS NULL;

-- Products never sold
SELECT p.id, p.title
FROM   products p
LEFT JOIN line_items li ON li.product_id = p.id
WHERE  li.id IS NULL;

ON vs WHERE — the silent INNER JOIN bug·sql

-- WRONG: WHERE on right side excludes unmatched left rows
SELECT u.name, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  o.total > 100;
-- Carol disappears because her right side is NULL, which fails > 100.

-- RIGHT: filter inside ON
SELECT u.name, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total > 100;
-- Carol stays; her order column is NULL.

External links

PostgreSQL JOIN types

Exercise

In any schema you have, find one orphan-style question (parents with no children) and write the LEFT JOIN + IS NULL query. Then add a count: how many children does each parent have, including parents with zero?

Progress

Progress is local-only — sign in to sync across devices.

← PreviousINNER JOIN: Stitching Tables Together Next →RIGHT JOIN, FULL JOIN, and Cross Joins

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.