Is there any difference between putting additional predicates on a JOIN statement vs. adding them as additional clauses in the WHERE statement?
在JOIN语句上添加其他谓词与在WHERE语句中将它们添加为附加子句之间有什么区别吗?
Example 1: Predicate on the WHERE clause
示例1:WHERE子句的谓词
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
示例2:JOIN语句的谓词
select emp.*
from Employee emp
left join Order o on emp.Id = o.EmployeeId and o.Cancelled = 0
4 个解决方案
#1
8
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)
使用第一个语句,由于WHERE条件,外连接有效地转换为内连接,因为它将过滤掉没有找到订单的employee表中的所有行(因为o.Cancelled将为NULL)
So the two statements don't do the same thing.
所以这两个陈述没有做同样的事情。
#2
3
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.
性能似乎如果谓词在JOIN上,那么“右”表会提前过滤。如果谓词是WHERE子句的一部分,那么所有结果都会返回并在返回结果集之前在结束时过滤一次。
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.
如果谓词是WHERE子句的一部分,则返回数据,然后在“right”值为null(即没有连接行)的情况下,整个行将不会在最终结果集中返回,因为谓词将进行比较值为null,因此返回false。
#3
3
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
#4
0
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
#1
8
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)
使用第一个语句,由于WHERE条件,外连接有效地转换为内连接,因为它将过滤掉没有找到订单的employee表中的所有行(因为o.Cancelled将为NULL)
So the two statements don't do the same thing.
所以这两个陈述没有做同样的事情。
#2
3
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.
性能似乎如果谓词在JOIN上,那么“右”表会提前过滤。如果谓词是WHERE子句的一部分,那么所有结果都会返回并在返回结果集之前在结束时过滤一次。
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.
如果谓词是WHERE子句的一部分,则返回数据,然后在“right”值为null(即没有连接行)的情况下,整个行将不会在最终结果集中返回,因为谓词将进行比较值为null,因此返回false。
#3
3
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
#4
0
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