联接条件“ N”与“ HERE”中的条件

Question

 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联接条件”时有什么区别？

通过在“ ON”子句和“ WHERE”子句中指定联接条件，我们在内部，左外部，右外部联接中是否获得相同的结果。请指教。

Answer 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

将条件放到WHERE子句中可以有效地使联接成为 INNER 联接，因为WHERE子句是行 过滤器 ， 在 建立联接 后才 应用。