Filter Condition Difference: WHERE Clause vs JOIN Condition

join;

Quick easy filter question.

What would be the difference in output, or what impact would it have moving a filter condition out of a WHERE clause into the Join condition.

For example:

Select a1.Name, a2.State
from student a1
left join location a2 on a1.name_id = a2.name_id
where a1.name LIKE 'A%'
and a2.state = 'New York';

To This:

Select a1.Name, a2.State
from student a1
left join location a2 on (a1.name_id = a2.name_id) and a2.state = 'New York'
where a1.name LIKE 'A%';

Thanks all.

Best Answer

1) will show student names that begins with "A" and whose location is New York.
2) will show all student names that begin with "A" , 'New York' if student state is New York, or null in other cases ( there is no corresponding state, or student state is not New York)

Difference between (1) and (2) - (1) will not have non-New York students.

Related Question