LEFT JOIN: Keeping Unmatched Rows

The shop carries 14 products, and the buying team wants a simple report: which ones have never sold a single unit? That question sounds like a job for the join you already know, pair each product with its order lines and read the list. But INNER JOIN has a blind spot: a product with no order lines produces no pairs, so it simply never shows up. The one product you were looking for is exactly the one the query hides. This lesson introduces LEFT JOIN, the join that keeps unmatched rows, and the classic pattern for finding them.

The Product That Vanishes

Start small. The Clothing category (category_id 4) has exactly two products: the Cotton T-Shirt and the Wool Beanie. Join them to order_items and you would expect to see both, right?

SELECT p.name, oi.order_id, oi.quantity
FROM products p
JOIN order_items oi ON oi.product_id = p.id
WHERE p.category_id = 4;

That is the key behavior to internalize: INNER JOIN only outputs rows where the ON condition found a match on both sides. A left row with no partner does not error, does not warn, does not appear as a blank line. It vanishes. For a query like “products and their sales” that is often fine. For a query like “which products never sold?” it is fatal, because the row you care about is precisely the one that gets dropped.

LEFT JOIN Keeps Every Left Row

Change one word and the missing row comes back. LEFT JOIN works like INNER JOIN for every row that matches, but it also guarantees that every row from the left table appears at least once. When a left row has no match, SQL still outputs it, and fills every column from the right table with NULL.

Which table is “left”? The one written before the JOIN keyword. In FROM products p LEFT JOIN order_items oi, products is the left table, so every product survives.

Flip between the two joins and notice which row the LEFT version adds, and what fills its right-hand columns.

SELECT p.name, oi.order_id, oi.quantity
FROM products p
INNER JOIN order_items oi ON oi.product_id = p.id
WHERE p.category_id = 4
ORDER BY p.id;

name | order_id | quantity
Cotton T-Shirt | 8 | 4

SELECT p.name, oi.order_id, oi.quantity
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE p.category_id = 4
ORDER BY p.id;

name | order_id | quantity
Cotton T-Shirt | 8 | 4
Wool Beanie | NULL | NULL

There it is: the Wool Beanie appears with NULL in order_id and NULL in quantity. Those NULLs are not an error and not missing data you need to fix. They are the answer: “this product exists, and nothing on the other side matched it.” Drop the WHERE clause and the same thing happens across the whole catalog: one row per order line (22 of them) plus one NULL-filled row for the Wool Beanie, 23 rows in total.

Finding What Never Matched

The toggle showed the unmatched row mixed in with all the matches. Usually you want only the misses: just the never-ordered products. The trick is to filter for the NULLs that LEFT JOIN produced. In an order_items row that really matched, oi.id is the table’s id column and is never NULL, so if it is NULL in the joined result, that NULL could only have come from a failed match. And remember from the NULL lesson: you must test it with IS NULL, because = NULL never matches anything.

As the result appears, notice that the WHERE clause tests a column from the right table. Why does a NULL there prove the product was never ordered?

SELECT p.name, p.price, p.stock
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

name | price | stock
Wool Beanie | 17.5 | 0

One row, out of the entire catalog: the Wool Beanie is the only product that has never been ordered. This LEFT JOIN plus IS NULL combination is one of the most used patterns in real reporting: customers who never bought, articles nobody read, employees assigned to no project. Same shape every time.

Try the pattern on other pairs of tables. An empty result is not a failure here, it is a finding:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

name

SELECT oi.id, oi.product_id
FROM order_items oi
LEFT JOIN products p ON p.id = oi.product_id
WHERE p.id IS NULL;

id | product_id

SELECT p.name, p.stock
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE p.stock = 0 AND oi.id IS NULL;

name | stock
Wool Beanie | 0

The first two come back empty, and that is good news: every customer in the shop has placed at least one order, and every order line points at a real product. The third one separates two ideas that sound alike: both the USB-C Hub and the Wool Beanie have stock 0, but the hub sold out through orders while the beanie has simply never sold. Only the beanie survives the oi.id IS NULL test.

Now write the finder yourself.

SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;