Why your LEFT JOIN acts like an INNER JOIN

You wrote LEFT JOIN because you wanted every row from the left table. The query runs, the numbers look reasonable, and some of the rows you wanted aren't there.

The usual cause is a filter on the right-hand table in the WHERE clause. This query is supposed to list all 40 customers with their count of orders discounted by 10% or more:

SELECT c.name, COUNT(o.id) AS discounted_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.discount >= 0.1
GROUP BY c.name
ORDER BY discounted_orders
LIMIT 5;

Look at the smallest count. No customer shows zero, and the full result has 30 rows rather than 40.

What the database actually does

FROM and JOIN run before WHERE. After the join, a customer with no orders has one row, and every column from orders on that row is NULL, which is exactly what LEFT JOIN promises.

Then WHERE runs. NULL >= 0.1 isn't false. It's unknown, and WHERE keeps only rows where the condition is true. The unmatched customers go, and so do customers whose orders all have a NULL discount. At that point you have the same result an INNER JOIN would have produced.

Fix: move the condition into ON

ON decides which rows match. An unmatched left row survives regardless, so filtering there can't remove customers.

SELECT c.name, COUNT(o.id) AS discounted_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.discount >= 0.1
GROUP BY c.name
ORDER BY discounted_orders
LIMIT 5;

All 40 customers are back and the zeros appear. COUNT(o.id) counts only non-NULL values, which is why an unmatched customer gets 0 and not 1. COUNT(*) would have given them 1.

When WHERE is the right place

A filter on the left table belongs in WHERE, because you do want those rows gone. Putting it in ON is the opposite bug: ON c.city = 'Tampa' on a LEFT JOIN keeps every customer and just stops matching orders for the ones outside Tampa.

There's one deliberate use of WHERE on the right table, the anti-join. IS NULL is true for unmatched rows, so this finds customers who have never ordered:

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

The rule: left-table filters go in WHERE, right-table filters go in ON, and the exception is IS NULL when you're looking for what's missing.