LEFT JOIN vs INNER JOIN — the difference, shown with real NULL rows

One sentence decides it: INNER JOIN keeps only the matches; LEFT JOIN keeps every left row and fills the misses with NULL. Everything else — row counts, the anti-join trick, the WHERE-vs-ON bug — falls out of that sentence. This post runs both joins on the same two tables so you can see the difference in actual rows, not in Venn diagrams.

The two tables

The examples use SQL Quest's e-commerce dataset: customers (16 rows) and orders (40 rows). Every order belongs to a customer — but 6 of the 16 customers have never placed an order. Those 6 are the entire difference between the two joins.

INNER JOIN — 40 rows

SELECT c.name, o.product, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

Result: 40 rows — one per order. A customer with 6 orders appears 6 times. A customer with no orders appears zero times: there is no match, so the row does not exist in the output. The 6 order-less customers have silently vanished.

That vanishing is not a bug — it is the definition. INNER JOIN answers: "show me the pairs that match." No pair, no row.

LEFT JOIN — 46 rows, and where the 6 extras come from

SELECT c.name, o.product, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Result: 46 rows — the same 40 matched rows, plus one row for each of the 6 customers with no orders. Here is what those rows actually look like:

Amy Taylor and Nina Patel survived the join with nothing to match — so every column that would have come from orders is NULL. This is what "NULL-padded" means: the left row is kept, the right side is padding.

One naming note, because it costs people real interview points: LEFT JOIN and LEFT OUTER JOIN are the same thing. The word OUTER is optional in every major database.

How to choose in one question

Ask: "is a left row without a match still an answer to my question?"

• Yes → LEFT JOIN. "All customers and their orders" — a customer with zero orders is still a customer; marketing specifically wants those rows. "Products with their reviews" — unreviewed products matter.
• No → INNER JOIN. "Revenue per order line" — an order line without a product row is data corruption, not an answer. "Payments matched to invoices" — the match is the subject.

Reports and dashboards lean LEFT (populations must not shrink because detail is missing). Pipelines and integrity checks lean INNER (only matched pairs are meaningful).

The WHERE-vs-ON trap that converts one into the other

The most common LEFT JOIN bug in production, and a favorite interview question. Watch the row counts — same tables, same intent, one word of difference:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed';   -- 37 rows: order-less customers vanished

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.status = 'completed';   -- 43 rows: 37 matches + 6 NULL-padded

Why: for the 6 unmatched customers, o.status is NULL — and NULL = 'completed' is not true, so WHERE throws those rows away. The filter ran after the join and deleted exactly the rows the LEFT JOIN existed to keep. You wrote a LEFT JOIN; you got an INNER JOIN with extra steps.

The anti-join: LEFT JOIN's best trick

"Which customers have never ordered?" INNER JOIN cannot answer this — it only sees matches. LEFT JOIN answers it in two lines, using the NULL padding as a signal:

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

Note this is the one legitimate right-table condition in WHERE — because being NULL is the thing you are selecting for. (A Platinum member who has never ordered, by the way, is exactly the row a retention team wants to see.)

▶ Practice: LEFT JOIN — Watch the NULLs Appear

FAQ

Is LEFT JOIN slower than INNER JOIN?

Marginally, sometimes — the engine must carry unmatched rows, and some reordering optimizations apply only to INNER JOINs. But this is a correctness choice, not a performance choice: if the unmatched rows belong in your answer, INNER JOIN is simply wrong. Choose by meaning, then index the join keys.

What about RIGHT JOIN?

A RIGHT JOIN B is B LEFT JOIN A with the tables swapped. Most teams standardize on LEFT and order the tables accordingly — one less direction to think about while reading.

Does COUNT change between the two?

Yes, and it is a classic bug: COUNT(*) after a LEFT JOIN counts the NULL-padded rows too (a customer with no orders counts 1), while COUNT(o.order_id) skips NULLs (that customer counts 0). After an INNER JOIN the two counts agree, because unmatched rows never made it in.