LEFT/RIGHT JOIN & Finding Missing Rows

📌 Definition:

📖 Detailed Explanation:

Where INNER JOIN hides unmatched rows, an OUTER JOIN preserves them. LEFT JOIN keeps every row from the left table; columns from the right table are NULL when no match exists. RIGHT JOIN is the mirror image (keep all right-table rows) and is less common because you can always rewrite it as a LEFT JOIN by swapping table order. The key QA pattern is anti-join for finding gaps: LEFT JOIN the two tables and filter WHERE right_table.key IS NULL to find left rows with no counterpart — users who never ordered, orders with no line items, records that failed to get a related row created. This directly surfaces data-integrity defects. A subtle trap: putting a condition on the right table in WHERE (rather than in the ON clause) turns a LEFT JOIN back into an effective INNER JOIN by filtering out the NULL rows, so conditions on the optional table belong in ON.

🔑 Key Points:

• LEFT JOIN keeps ALL left-table rows; right columns are NULL when unmatched
• RIGHT JOIN is the mirror; usually rewritten as a LEFT JOIN by swapping order
• Anti-join pattern: LEFT JOIN ... WHERE right.key IS NULL finds missing related rows
• Great for integrity checks: users with no orders, orders with no items
• Filtering the right table in WHERE silently turns a LEFT JOIN into an INNER JOIN

🌍 Real-World Example:

📎 Code Example:

-- Users who have never placed an order (anti-join)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Orders missing their line items (integrity gap)
SELECT o.id
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE oi.id IS NULL;

-- TRAP: this condition on the right table makes it act like an INNER JOIN
-- SELECT ... FROM users u LEFT JOIN orders o ON o.user_id = u.id
-- WHERE o.status = 'confirmed';   -- drops users with no orders!
-- Correct: put the optional-table condition in the ON clause:
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'confirmed';