T-SQL - Left Outer Joins - Filters in the where clause versus the on clause

If you filter the left outer joined table in the WHERE clause then you are in effect creating an inner join

See also this wiki page: WHERE conditions on a LEFT JOIN


with LEFT OUTER JOINS, you must filter in the ON clause or use this:

WHERE
    (LeftJoinTable.ID IS NULL OR LeftJoinTable.Col1=YourFilter)

if you just filter in the WHERE:

WHERE 
    LeftJoinTable.Col1=YourFilter

you will discard the parent joining row whenever there isn't a LeftJoinTable.ID (making the join an INNER JOIN).

By placing the filter in the ON, you cause the elimination of the LEFT JOIN row but not the elimination of the parent joining row, that is just how it works.

EDIT base don OP's comment
the only way to filter a a LEFT OUTER JOIN table is in the ON clause, unless you want to use an OR like I show in the first code example above. There is nothing wrong filtering a LEFT OUTER JOIN in the ON clause, this is how you do it.


As the query is written, it makes sense to put the join in the ON clause, since you specifically only want to join on values in group '2' from table 1.

The alternative is to prefilter table1 to the group you are interested in, like this

select  t1Group.groupby,
        t1Group.number as [T1_Number],
        table2.number as [T2_Number]
from    table2
        LEFT OUTER join (SELECT * FROM table1 WHERE groupby=2) t1Group
            on table2.number = t1Group.number
WHERE   t1Group.number IS NULL

Tags:

Sql

Left Join