加入条件"ON"与"WHERE"中的 [英] join condition "ON" vs in "WHERE"

问题描述

 SELECT *
 FROM Customers c
 INNER JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 AND c.State = 'NY'
 INNER JOIN Accounts a
 ON ca.AccountID = a.AccountID
 AND a.Status = 1

等效:

 SELECT *
 FROM Customers c
 INNER JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 INNER JOIN Accounts a
 ON ca.AccountID = a.AccountID
 WHERE c.State = 'NY'
 AND a.Status = 1

左加入:

 SELECT *
 FROM Customers c
 LEFT JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 AND c.State = 'NY'
 LEFT JOIN Accounts a
 ON ca.AccountID = a.AccountID
 AND a.Status = 1

等效:

 SELECT *
 FROM Customers c
 LEFT JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 LEFT JOIN Accounts a
 ON ca.AccountID = a.AccountID
 WHERE c.State = 'NY'
 AND a.Status = 1

右加入

 SELECT *
 FROM Customers c
 RIGHT JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 AND c.State = 'NY'
 RIGHT JOIN Accounts a
 ON ca.AccountID = a.AccountID
 AND a.Status = 1

等效:

 SELECT *
 FROM Customers c
 RIGHT JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 RIGHT JOIN Accounts a
 ON ca.AccountID = a.AccountID
 WHERE c.State = 'NY'
 AND a.Status = 1

当我们在"WHERE"子句中指定联接条件与"ON联接条件"时有什么区别?

What difference it makes when we specify the join condition in "WHERE" clause vs "ON join condition"?

通过在"ON"子句和"WHERE"子句中指定联接条件，我们在内部，左外部，右外部联接中得到相同的结果吗?请告知.

Do we get same results in inner, left outer, right outer join's by specifying the join conditions in "ON" clause vs in "WHERE" clause. Please advise.

推荐答案

好吧，所谓的等效"与外部联接不是等效的.让我们以左联接为例.

Well, what you call "equivalent" is not an equivalent for outer joins. Let's take the left join for example.

加入的条件:

SELECT * FROM Customers c
LEFT JOIN CustomerAccounts ca ON ca.CustomerID = c.CustomerID AND c.State = 'NY'
LEFT JOIN Accounts a ON ca.AccountID = a.AccountID AND a.Status = 1

与位置:

SELECT * FROM Customers c
LEFT JOIN CustomerAccounts ca ON ca.CustomerID = c.CustomerID
LEFT JOIN Accounts a ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
AND a.Status = 1

将条件放入WHERE子句中可以有效地使联接成为 INNER 联接，因为WHERE子句是 filter 行，在之后联接已经完成.

Putting the conditions into the WHERE clause effectively makes the joins INNER joins, because the WHERE clause is a row filter that is applied after the joins have been made.

这篇关于加入条件"ON"与"WHERE"中的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！