Understanding LEFT JOIN ON vs WHERE Conditions in SQL

When writing SQL, the author initially expected that adding conditions after a LEFT JOIN's ON clause would merge two matching rows into one, but discovered that the query still returned two rows.

Key observation: The ON clause combined with AND does not filter the number of result rows; it only determines whether columns from the right‑hand table are displayed, while rows from the left table are always returned.

Regardless of whether the condition after AND references A.id=1 or B.id=1 , all rows from table A appear, and matching rows from table B (or nulls) are shown according to the condition.

Example queries:

select * from student s left join class c on s.classId=c.id order by s.id

select * from student s left join class c on s.classId=c.id and s.name="张三" order by s.id

select * from student s left join class c on s.classId=c.id and c.name="三年级三班" order by s.id

When a database joins two or more tables, it first creates an intermediate temporary table and then returns that table to the user.

Difference between ON and WHERE in a LEFT JOIN:

ON conditions are applied while building the temporary table; they do not remove rows from the left table.

WHERE conditions are applied after the temporary table is built, filtering rows and potentially discarding the LEFT JOIN effect.

Consider two tables, tab1 and tab2 , with the following queries:

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

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

Process of the first query:

Intermediate table built using ON condition tab1.size = tab2.size .

After the intermediate table is created, the WHERE clause tab2.name='AAA' filters the rows, removing any left‑table rows that do not satisfy the condition.

Process of the second query:

Intermediate table built using combined ON condition tab1.size = tab2.size and tab2.name='AAA' .

Even if the combined condition is false for a left‑table row, the row is still returned with NULLs for the right‑hand columns because it is a LEFT JOIN.

The essential reason behind these results is the special behavior of LEFT, RIGHT, and FULL joins: they always return rows from the preserved side regardless of the truth of the join condition, whereas INNER JOIN does not have this property, making ON and WHERE interchangeable for it.