SQL INNER JOIN vs LEFT JOIN: When Rows Disappear — sqlfmt

sqlfmt▍

SQL INNER JOIN vs LEFT JOIN: When Rows Disappear — sqlfmt

Key takeaways

INNER JOIN removes left rows without a qualifying right-side match.
LEFT JOIN keeps all left rows and supplies NULL for missing right-side data.
A right-table predicate in WHERE can accidentally undo a LEFT JOIN.

What result does each join return?

INNER JOIN is for matched relationships. LEFT JOIN is for complete left-side coverage. Left means the table written before JOIN.

Same relationship, different result sets

-- matched customers only
SELECT c.id, c.name, o.id AS order_id
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

-- every customer
SELECT c.id, c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

Microsoft documents how a left outer join returns unmatched rows with NULL right-side values.

The WHERE-clause trap

WHERE o.status = 'paid' after a LEFT JOIN removes unmatched rows because their o.status is NULL. Put the right-side condition in ON when those left rows must remain.

Keep every customer while matching paid orders

SELECT c.id, c.name, o.id AS paid_order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid';

Use this SQL JOIN formatting guide to keep the relationship readable in review.

When should you use each join?

Use INNER JOIN when both records must exist.
Use LEFT JOIN for optional relationships and completeness checks.
Count a right-table primary key, not COUNT(*), when you need actual matches.

Frequently asked questions

Does LEFT JOIN return every row from both tables?

No. It guarantees every left-table row. FULL OUTER JOIN preserves unmatched records from both sides where supported.

Can LEFT JOIN duplicate a left row?

Yes. A left row appears once per matching right row. Aggregate deliberately when one output row per left record is required.