Filter Condition Difference - Where Clause vs Join Condition

Question

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.

Answer 1

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.

Answer 2

@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".

1. 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.

2. 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.

3. 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.