Left Join: ON vs WHERE – Understanding the Crucial Difference

When using LEFT JOIN, the ON condition is evaluated while the database builds the intermediate result set; it does not filter out rows from the left table, which are always returned. By contrast, a WHERE condition is applied after the intermediate table is created, and rows that do not satisfy the condition are removed, even if they come from the left side.

Example queries demonstrate the behavior:

SELECT *
FROM student s
LEFT JOIN class c ON s.classId = c.id
ORDER BY s.id

Adding a condition to the ON clause does not reduce the number of rows from student:

SELECT *
FROM student s
LEFT JOIN class c ON s.classId = c.id AND s.name = '张三'
ORDER BY s.id

Similarly, filtering on the right‑hand table in the ON clause only affects the columns from class, while all rows from student remain:

SELECT *
FROM student s
LEFT JOIN class c ON s.classId = c.id AND c.name = '三年级三班'
ORDER BY s.id

The article then explains the internal mechanism: the database first creates a temporary table by joining the two tables according to the ON expression, then applies any WHERE predicates to that temporary table. Because LEFT JOIN guarantees that rows from the left table appear even when the join condition is false, the ON clause cannot eliminate them.

Two concrete SQL statements illustrate the difference:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'

In this query the ON clause matches rows by size, then the WHERE clause filters the result to keep only rows where tab2.name='AAA', potentially discarding left‑table rows.

select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

Here the condition on tab2.name is part of the ON clause, so the join still returns all left‑table rows, but the right‑hand columns are null when tab2.name does not match.

The article walks through each query step by step, showing the intermediate temporary table and the final filtered result with accompanying diagrams (images omitted for brevity). It concludes that the key reason for the observed behavior is the special nature of LEFT, RIGHT, and FULL joins: they always preserve rows from the side specified, regardless of whether the ON condition is true. In contrast, INNER JOIN does not have this property, so placing a condition in ON or WHERE yields the same result set.