Why LEFT JOIN Still Returns All Left Rows: ON vs WHERE Explained

When writing SQL, I tried to use A LEFT JOIN B ON ... AND ... to merge two rows into one, but still got two rows.

I later discovered that the AND after ON does not filter the result count; it only determines whether rows from table B are shown, while rows from table A are always returned.

Regardless of whether the condition after AND is A.id = 1 or B.id = 1, all records from table A appear, with matching B records (or nulls) for the specified condition.

Example SQL:

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

Adding an AND condition to the ON clause:

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

Using ON versus WHERE in a LEFT JOIN:

The ON condition is applied while generating the temporary table; it does not remove rows from the left table, even if the condition is false.

The WHERE condition filters the temporary table after it is created, so rows that do not satisfy the condition are removed entirely.

Assume two tables:

Table 1:

Table 2:

Two SQL 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 uses ON condition: tab1.size = tab2.size.

After the temporary table is created, the WHERE clause filters rows where tab2.name='AAA'.

Process of the second query:

Intermediate table uses combined ON condition: tab1.size = tab2.size AND tab2.name='AAA'.

Note: Even if the condition is false, LEFT JOIN still returns rows from the left table.

The special nature of LEFT JOIN, RIGHT JOIN, and FULL JOIN is that they always return rows from the respective side(s) regardless of the ON condition, whereas INNER JOIN does not have this behavior; placing the condition in ON or WHERE yields the same result set.