T-SQL: Additional predicates on JOINs vs. the WHERE clause

Is there any difference between putting additional predicat开发者_运维百科es on a JOIN statement vs. adding them as additional clauses in the WHERE statement?

Example 1: Predicate on the WHERE clause

select emp.*
from Employee emp
left join Order o on emp.Id = o.EmployeeId
where o.Cancelled = 0

Example 2: Predicate on the JOIN statement

select emp.*
from Employee emp
left join Order o on emp.Id = o.EmployeeId and o.Cancelled = 0

With the first statement the outer join is effectively turned into an inner join because of the WHERE condition as it will filter out all rows from the employee table where no order was found (because o.Cancelled will be NULL then)

So the two statements don't do the same thing.

I already got the answers from some of my colleagues, but in case they don't post it here, I'll add an answer myself.

Both of these examples assume that the predicate is comparing a column on the "right" table with a scalar value.

Performance
It seems that if the predicate is on the JOIN, then the "right" table is filtered in advance. If the predicate is part of the WHERE clause, then all results come back and are filtered once at the end before returning the resultset.

Data Returned
if the predicate is part of the WHERE clause, then in the situation where the "right" value is null (i.e. there is no joining row) then the entire row will not be returned in the final resultset, because the predicate will compare the value with null and therefore return false.

Just to address the case that the additional predicate is on a column from the left hand table this can still make a difference as shown below.

WITH T1(N) AS
(
SELECT 1 UNION ALL
SELECT 2
), T2(N) AS
(
SELECT 1 UNION ALL
SELECT 2
)
SELECT T1.N, T2.N, 'ON' AS Clause
FROM T1 
LEFT JOIN T2 ON T1.N = T2.N AND T1.N=1
UNION ALL
SELECT T1.N, T2.N, 'WHERE' AS Clause
FROM T1 
LEFT JOIN T2 ON T1.N = T2.N 
WHERE T1.N=1

Returns

N           N           Clause
----------- ----------- ------
1           1           ON
2           NULL        ON
1           1           WHERE

Here is another example ( four cases )

insert into #tmp(1,"A")
insert into #tmp(2,"B")

select "first Query", a.*,b.* from #tmp a LEFT JOIN #tmp b
on a.id =b.id
and  a.id =1

union all

select "second Query", a.*,b.* from #tmp a LEFT JOIN #tmp b
on a.id =b.id
where a.id =1

union all

select "Third Query", a.*,b.* from #tmp a LEFT JOIN #tmp b
on a.id =b.id
and  b.id =1

union all

select "Fourth Query", a.*,b.* from #tmp a LEFT JOIN #tmp b
on a.id =b.id
where  b.id =1

Results:

first Query       1      A      1      A
first Query       2      B      NULL   NULL
second Query      1      A      1      A
Third Query       1      A      1      A
Third Query       2      B      NULL   NULL
Fourth Query      1      A      1      A
Fourth Query      1      A      1      A