Unit 05.00: INNER, LEFT, and what each one drops

Unit ID: SQL-M05-U01 - Estimated active time: 14-17 minutes Objective: choose the join type that matches the question, and name the rows each one removes.

A join type is a decision about missing matches

• INNER JOIN keeps rows that match on both sides.
• LEFT JOIN keeps every left row, filling the right side with NULL when there is no match.

Our data makes the difference concrete. There are 4,812 customers, but only 1,000 have ever ordered:

SELECT COUNT(DISTINCT customer_id) FROM orders;
-- 1000

So an inner join between customers and orders silently discards 3,812 customers:

-- "Customers and their orders" - but only customers who ordered
SELECT COUNT(*) FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;
-- 1000

If the question was "average orders per customer", using the inner join gives 1.0 - because every customer without an order vanished before the average was computed. The honest answer involves 4,812 customers, most of whom ordered nothing.

Finding the rows the inner join would hide

LEFT JOIN plus an IS NULL test on the right side is the standard "find what is missing" pattern:

SELECT COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- 3812

3,812 customers have never placed an order. That is a genuine business fact your inner join was throwing away.

The trap: ON versus WHERE in a LEFT JOIN

These look similar and mean completely different things:

-- Restricts what counts as a match; all customers still returned
SELECT COUNT(*) FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'completed';
-- 4812

-- Filters AFTER the join, which discards the unmatched rows
SELECT COUNT(*) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed';
-- 988

The second has quietly become an inner join. Any condition on the right table placed in WHERE will eliminate the NULL-filled rows the LEFT JOIN just created - because NULL = 'completed' is not TRUE.

Practice

Write a query listing each country with the number of customers who have never ordered.

SELECT COALESCE(c.country, 'Unknown') AS country,
       COUNT(*)                       AS never_ordered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
GROUP BY 1
ORDER BY never_ordered DESC;

Takeaway

INNER answers "where both exist", LEFT answers "everything on the left, matched where possible". A condition on the right table belongs in ON, not WHERE, unless you intend to drop the unmatched rows.

---

Mark complete