Understanding LEFT JOIN ON vs WHERE Conditions in SQL

The author initially tried to use a LEFT JOIN with an additional A left B join on and condition, expecting two rows to merge into one, but discovered that the result still contained two rows.

It turns out that the join on and clause does not filter the number of result rows; the left‑hand table (A) always appears, and the condition after AND only determines whether matching rows from the right‑hand table (B) are shown.

Regardless of whether the condition after AND is A.id=1 or B.id=1 , all records from table A are returned, with the corresponding B records (or null) attached.

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

The same query with an additional filter on the left table:

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

And with a filter on the right table:

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 a temporary intermediate table and then returns that table to the user.

For a LEFT JOIN , the difference between ON and WHERE conditions is:

The ON condition is applied while generating the temporary table; it does not discard left‑hand records even if the condition is false.

The WHERE condition is applied after the temporary table is built; at this point the LEFT JOIN guarantee is lost, and rows that do not satisfy the condition are filtered out.

Assume two tables, tab1 and tab2 . Two SQL statements illustrate the effect:

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')

First statement process:

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

Apply WHERE filter: tab2.name='AAA' .

Second statement process:

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

Note: Even if the condition is false, the left‑hand record is still returned.

The special behavior of LEFT JOIN , RIGHT JOIN , and FULL JOIN is that they always return records from the designated side(s) regardless of the truth of the ON condition; FULL JOIN combines both sides. In contrast, an INNER JOIN does not have this property, and placing a condition in ON or WHERE yields the same result set.