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

Problem Overview

When writing SQL, the author expected that adding conditions after a LEFT JOIN using AND would merge two rows into one, but observed that both rows still appear.

Behavior of LEFT JOIN with ON and AND

The AND placed after the ON clause does not filter the result set; it only determines which columns from the right table are displayed. The left‑table rows are always returned.

Difference Between ON and WHERE in LEFT JOIN

ON condition is applied while the intermediate temporary table is being built; it does not eliminate rows from the left table even if the condition is false.

WHERE condition is applied after the temporary table is created; it filters rows, and the LEFT JOIN semantics are lost because rows that do not satisfy the WHERE clause are removed.

Example Queries

Basic LEFT JOIN:

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

LEFT JOIN with an additional 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;

LEFT JOIN with an additional 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;

These queries produce the same number of rows as the basic query; the extra condition only controls which columns from class are shown.

Two Detailed Scenarios

Scenario 1 – Condition in WHERE clause:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA';

Process:

Intermediate table built using ON condition tab1.size = tab2.size.

Resulting temporary table is filtered by the WHERE clause tab2.name='AAA', removing rows that do not match.

Scenario 2 – Condition combined with AND in ON clause:

select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA');

Process:

Intermediate table built using combined ON condition tab1.size = tab2.size and tab2.name='AAA'.

No additional filtering step; all left‑table rows are retained, and matching right‑table rows are shown.

Key Takeaway

The special behavior of LEFT, RIGHT, and FULL joins is that they always return rows from the preserved side regardless of whether the join condition evaluates to true; only the columns from the other side may be null. In contrast, an INNER JOIN does not have this property, and placing a condition in ON or WHERE yields the same result set.