Preserving Data: LEFT JOIN

The Missing Customers

My INNER JOIN report looked great, but the ops team noticed something: *"We had 10,000 orders, but your report only shows 9,500. Where are the other 500?"*

The missing orders were "Guest Checkouts"—they had no `customer_id`. The INNER JOIN silently dropped them because there was no match.

The Quest: Keep the Left Side

A `LEFT JOIN` (or `LEFT OUTER JOIN`) keeps **all rows from the left table**, even if there is no matching row in the right table. If there's no match, the columns from the right table will be filled with `NULL`.

The Implementation: The Syntax

SELECT

o.order_id,

o.order_amount,

c.name -- This will be NULL for guest orders

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

The Power of Finding the "Missing"

LEFT JOIN is your best friend for finding **orphan records**—data that *should* have a match but doesn't.

-- Find all orders that DON'T have a matching customer

SELECT o.*

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id

WHERE c.id IS NULL;

The "Oops" Moment

I once used a LEFT JOIN expecting all my data to be there, but my `WHERE` clause inadvertently filtered out the NULLs: `WHERE c.country = 'USA'`. This turned my LEFT JOIN into an INNER JOIN!

**Pro Tip**: If you filter on a column from the RIGHT side of a LEFT JOIN, you must say `...OR c.id IS NULL` to preserve the NULLs.

The Victory

The ops team now had a complete report. The 500 guest orders were marked as "Guest Checkout" using `COALESCE(c.name, 'Guest Checkout')`. No data was lost.

Your Task for Today

Use a LEFT JOIN and then add `WHERE right_table.column IS NULL` to find all the orphan records—the data that's missing a match.

*Day 34: The Opposite View—RIGHT JOIN.*