SQL 为什么以及何时在 WHERE 子句中带有条件的 LEFT JOIN 不等同于 ON 中的相同 LEFT JOIN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15706112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?
提问by Kamel Keb
I'm experiencing a very confusing situation that makes me question all my understanding of joins in SQL Server.
我遇到了一个非常令人困惑的情况，这让我质疑我对 SQL Server 中的联接的所有理解。
SELECT t1.f2
FROM t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something
Does not give the same results as :
不会给出与以下相同的结果：
SELECT t1.f2
FROM t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2
WHERE t2.f3 > something
Can please someone help by telling if this two queries are supposed to be equivalent or not?
可以请人帮忙告诉这两个查询是否应该等效吗？
Thx
谢谢
回答by Andomar
The onclause is used when the joinis looking for matching rows. The whereclause is used to filter rows after all the joining is done.
该on子句在join查找匹配行时使用。该where子句用于在所有连接完成后过滤行。
An example with Disney toons voting for president:
以迪士尼卡通人物为总统投票的例子：
declare @candidates table (name varchar(50));
insert @candidates values
('Obama'),
('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values
('Mickey Mouse', 'Romney'),
('Donald Duck', 'Obama');
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
and v.voter = 'Donald Duck'
This still returns Romneyeven though Donalddidn't vote for him. If you move the condition from the onto the whereclause:
Romney即使Donald没有投票给他，这仍然会返回。如果将条件从 the 移动on到where子句：
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
where v.voter = 'Donald Duck'
Romneywill no longer be in the result set.
Romney将不再出现在结果集中。
回答by John Woo
Both are literally different.
两者在字面上是不同的。
The first query does the filtering of table t2before the joining of tables take place. So the results will then be join on table t1resulting all the records of t1will be shown on the list.
第一个查询t2在加入表之前对表进行过滤。因此，结果将被连接到表上，t1结果所有记录都t1将显示在列表中。
The second one filters from the total result after the joining the tables is done.
完成连接表后，第二个从总结果中过滤。
Here's an example
这是一个例子
Table1
表格1
ID Name
1 Stack
2 Over
3 Flow
Table2
表2
T1_ID Score
1 10
2 20
3 30
In your first query, it looks like this,
在您的第一个查询中，它看起来像这样，
SELECT a.*, b.Score
FROM Table1 a
LEFT JOIN Table2 b
ON a.ID = b.T1_ID AND
b.Score >= 20
What it does is before joining the tables, the records of table2are filtered first by the score. So the only records that will be joined on table1 are
它的作用是在加入表格之前，先按table2分数过滤记录。所以将在 table1 上连接的唯一记录是
T1_ID Score
2 20
3 30
because the Scoreof T1_IDis only 10. The result of the query is
因为ScoreofT1_ID只有10。查询的结果是
ID Name Score
1 Stack NULL
2 Over 20
3 Flow 30
While the second query is different.
而第二个查询不同。
SELECT a.*, b.Score
FROM Table1 a
LEFT JOIN Table2 b
ON a.ID = b.T1_ID
WHERE b.Score >= 20
It joins the records first whether it has a matching record on the other table or not. So the result will be
它首先连接记录，无论它在另一个表上是否有匹配的记录。所以结果将是
ID Name Score
1 Stack 10
2 Over 20
3 Flow 30
and the filtering takes place b.Score >= 20. So the final result will be
并进行过滤b.Score >= 20。所以最终的结果将是
ID Name Score
2 Over 20
3 Flow 30
回答by Ryan
In the first case, results in t2is filtered as part of the join.
在第一种情况下，结果t2作为连接的一部分被过滤。
In the second case, there could be more rows available from t2.
在第二种情况下，可能有更多行可用t2。
Essentially, the set of records joined in the two queries will not be the same.
本质上，这两个查询中连接的记录集不会相同。
回答by Brian
It does make a difference because in second case you are applying the where AFTER it does the left join
它确实有所作为，因为在第二种情况下，您正在应用 where AFTER 它进行左连接
回答by mr_eclair
CREATE TABLE Company
(
CompanyId TinyInt Identity Primary Key,
CompanyName Nvarchar(50) NULL
)
GO
INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO
CREATE TABLE Candidate
(
CandidateId tinyint identity primary key,
FullName nvarchar(50) NULL,
CompanyId tinyint REFERENCES Company(CompanyId)
)
GO
INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO
SELECT * from Company c
SELECT * from Candidate c
-- A simple left outer Join
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId
--Left Outer Join ON and AND condition fetches 5 rows wtih NULL value from right side table
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId
AND c.CompanyName = 'DELL'
--Left Outer Join ON and where clause fetches only required rows
SELECT * FROM Company c LEFT OUTER JOIN Candidate c2
ON c.CompanyId = c2.CompanyId
AND c.CompanyName = 'DELL'
WHERE c.CompanyName='IBM'
