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:

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

Adding an

AND

condition to the

ON

clause:

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

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:

<code>SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.size = tab2.size) WHERE tab2.name='AAA'</code>

<code>SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.size = tab2.size AND tab2.name='AAA')</code>

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.