Understanding LEFT JOIN ON vs WHERE Conditions in SQL

The author tried to merge two rows into one by using a LEFT JOIN with an additional AND condition, but still obtained two rows.

It was later discovered that a LEFT JOIN with an AND in the ON clause does not filter the number of result rows; the left‑table rows are always returned, while the right‑table rows appear only if the AND condition is satisfied.

Whether the AND condition references a column from the left table (e.g., A.id=1) or the right table (e.g., B.id=1), all rows from the left table are displayed, and matching rows from the right table are shown when the condition holds.

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 it to the user.

Difference between ON and WHERE in a LEFT JOIN:

1. The ON condition is applied while generating the intermediate table; regardless of whether the ON condition evaluates to true, rows from the left table are always included.

2. The WHERE condition filters the intermediate table after it is created; at this point the LEFT JOIN semantics no longer apply, and rows that do not satisfy the WHERE clause are removed.

Assume two tables, tab1 and tab2 . The following two queries illustrate the difference:

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:

1. Intermediate table ON condition: tab1.size = tab2.size

2. Apply WHERE filter on the intermediate table: tab2.name='AAA'

Process of the second query:

1. Intermediate table ON condition includes the filter: tab1.size = tab2.size and tab2.name='AAA'

Even if the combined ON condition is false, rows from the left table are still returned.

The key reason for these results is the special nature of LEFT, RIGHT, and FULL joins: they always return rows from the preserved side (left or right) regardless of the ON condition's truth, whereas INNER JOIN does not have this property, making ON and WHERE conditions interchangeable for inner joins.