@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 a TableB
field that doesn't allow for a NULL value in that field, all the TableA
rows without a matching TableB
row will be excluded.
Example: WHERE b.State = 'New York'
- TableA
rows with no matching TableB
rows would have B.Column1
as NULL. Since NULL = 'New York'
is not TRUE, none of the TableA
(student
) rows without a matching TableB
(location
) row meet the criteria in the WHERE
clause.
In effect, this makes the LEFT JOIN
an INNER 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 matching TableB
row will still be included. However, so will TableA
rows with a matching TableB
row where Column1
is set to NULL (in your case, student
rows with a matching location
row, where the location.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 the JOIN
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 matching TableB
(location
) row; where there is a matching TableB
row, the matched rows from TableA
and TableB
will only be included if b.State
is "New York".
Second, include your check in the WHERE
clause, but use the JOIN
column in TableB
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 way b.name_id
can be NULL is if there was no match found for the JOIN
in TableB
. Again, TableA
rows without a TableB
match are included (because b.name_id
will always be NULL for those rows). With our assumption, where TableA
has a matching TableB
row, b.name_id
will never be NULL, so b.State
must be "New York" for this TableA
and TableB
matched pair of rows to be included.