Why LEFT JOIN Still Returns All Left‑Table Rows Even with ON AND Conditions

When writing SQL, I expected that adding extra conditions after ON in a LEFT JOIN (e.g., ON ... AND ...) would merge two rows into one, but the query still returned two rows.

The JOIN ... ON ... AND ... clause does not filter the number of result rows; it only determines whether columns from the right 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, with matching rows from B (or null) shown according to the condition.

Example query:

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

Adding a condition on the left table:

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

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

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

ON condition: Applied while generating the temporary table; it does not affect the inclusion of left‑table rows, which are always kept.

WHERE condition: Applied after the temporary table is built; it filters the result set, so the left‑join guarantee is lost and rows not satisfying the condition are removed.

Assume two tables and the following two SQL statements:

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

The first statement applies the filter in WHERE, so rows from tab1 without a matching tab2 row are removed. The second statement keeps all left‑table rows because the filter is part of the ON clause.

The key reason for these results is the special behavior of LEFT JOIN, RIGHT JOIN, and FULL JOIN: regardless of whether the condition on the ON clause is true, they always return rows from the preserved side (left or right). FULL JOIN combines both sides. In contrast, INNER JOIN does not have this special property; placing a condition in ON or WHERE yields the same result set.