LEFT JOIN filters in ON versus WHERE

Problem and reasoning

LEFT JOIN first uses ON to decide which right rows may match, and unmatched right columns become NULL. WHERE then filters the completed result. Requiring a right column value in WHERE removes those NULL rows and can make the query behave like INNER JOIN.

When every left row must remain, put predicates that only qualify right-side matches in ON. Use WHERE when the requirement truly excludes left rows without a qualifying match.

Do not choose by memorized syntax alone. Ask whether a left-side entity with no match should still be present.

Examples

Sample table

Table：orders

orders
id	customer_id
1	101
2	102
3	101
4	104

Table：customers

customers
id	name
101	Alice
102	Bob
103	Cindy

Before / Not recommended

SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id WHERE c.name = 'Alice' ORDER BY o.id;

Before result

result
id	name
1	Alice
3	Alice

After / Recommended

SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id AND c.name = 'Alice' ORDER BY o.id;

After result

result
id	name
1	Alice
2	NULL
3	Alice
4	NULL

Why it is better：WHERE removes unmatched right-side rows; moving the condition into ON limits matching while preserving every order.

Was this explanation clear?

Keep learning

Continue with related topics or compare database syntax in the SQL dialect translator.