T-SQL: additional predicates for JOINs versus WHERE clause

Question

Is there a difference between putting extra predicates in a JOIN statement and adding them as extra clauses in a WHERE statement?

Example 1: Predicate in a WHERE clause

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

Example 2: Predicate in JOIN Statement

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

Answer 1

With the first statement, the outer join effectively becomes the inner join due to the WHERE clause, since it will filter out all the rows from the employee table where no order was found (since o.Cancelled will be NULL then)

Thus, both statements do not do the same.

Answer 2

I have already received answers from some of my colleagues, but in case they do not send it here, I will add the answer myself.

Both of these examples assume that the predicate compares a column in the right table with a scalar value.

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

Returned data
if the predicate is part of the WHERE , then in the situation where the “correct” value is null (ie there is no connection string), the whole string will not be returned in the final resultet, because the predicate will compare the value with null and therefore returns false

Answer 3

Just to consider the case where an additional predicate is in a column from the left table, it can still matter, 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

 NN Clause ----------- ----------- ------ 1 1 ON 2 NULL ON 1 1 WHERE 

Answer 4

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