Filter Condition Difference - Where Clause vs Join Condition
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.
@a1ex07 's answer is entirely correct. However, let's provide a bit more general answer.
When you have a TableA a LEFT JOIN TableB b
scenario, then you have to be careful how you use TableB
fields in your WHERE
clause.
For any rows in TableA
where there is no matching row in TableB
, all fields in TableB
will be set to NULL.
Let's look at your example.
We'll assume that that you want all rows from TableA
(student
) that either have no matching row in TableB
(location
), or that have a matching row in TableB
where b.Column1
(location.State
) equals "New York".
If your
WHERE
clause includes a check on aTableB
field that doesn't allow for a NULL value in that field, all theTableA
rows without a matchingTableB
row will be excluded.Example:
WHERE b.State = 'New York'
-TableA
rows with no matchingTableB
rows would haveB.Column1
as NULL. SinceNULL = 'New York'
is not TRUE, none of theTableA
(student
) rows without a matchingTableB
(location
) row meet the criteria in theWHERE
clause.In effect, this makes the
LEFT JOIN
anINNER JOIN
.If you do allow for
TableB
values to be NULL, you need to be careful that you don't allow in more values than you mean.If you change the above example
WHERE
clause to:WHERE (b.State = 'New York' OR b.State IS NULL)
Then
TableA
rows without a matchingTableB
row will still be included. However, so willTableA
rows with a matchingTableB
row whereColumn1
is set to NULL (in your case,student
rows with a matchinglocation
row, where thelocation.State
is NULL). That may not be the intent.To actually meet our assumed intent, you have at least two options:
First, you can put the restriction on
TableB
rows in theJOIN
condition:FROM TableA a LEFT JOIN TableB b ON (a.name_id = b.name_id AND b.State = 'New York')
This lets through all
TableA
(student
) rows where there's no matchingTableB
(location
) row; where there is a matchingTableB
row, the matched rows fromTableA
andTableB
will only be included ifb.State
is "New York".Second, include your check in the
WHERE
clause, but use theJOIN
column inTableB
to allow for NULLs:FROM TableA a LEFT JOIN TableB b ON (a.name_id = b.name_id) WHERE (b.State = 'New York' OR b.name_id IS NULL)
This assumes that
a.name_id
cannot be NULL. Then, the only wayb.name_id
can be NULL is if there was no match found for theJOIN
inTableB
. Again,TableA
rows without aTableB
match are included (becauseb.name_id
will always be NULL for those rows). With our assumption, whereTableA
has a matchingTableB
row,b.name_id
will never be NULL, sob.State
must be "New York" for thisTableA
andTableB
matched pair of rows to be included.