SQL语句中的ON v WHERE是什么意思?

Question

请查看下面的表格：

顾客表：

ID
Name

订单表：

ID
CustomerID

一个客户可以下0、1或多个订单。请看下面的SQL查询语句：

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.CustomerID 
WHERE CustomerID IS NULL

并且

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.CustomerID AND
      CustomerID IS NULL

这两个查询有什么区别？开发人员什么时候会使用其中一种技术而不是另一种技术呢？

我想在线上应该有其他类似的问题，但我没有找到答案，所以提出了这个问题。

- w0051977

---

@Royi Namir，我之前搜索时没有遇到过那个问题。你链接中的问题特别涉及Oracle。我的问题更多地涉及SQL Server，所以我已经打了标签。 - w0051977

好的。你没有提到它。:-) - Royi Namir

提出的重复问题是关于INNER JOIN的；而这个问题则是关于LEFT OUTER JOIN的。考虑因素不同，两个问题并不重复。（Oracle与SQL Server的区别在实质上是不重要的。） - Jonathan Leffler

问题：Customer.ID和Order.ID真的是同一个属性吗？对于任何正确设计的数据库来说都是错误的。而且CustomerID在哪个表中？这里更有可能是想要Customer.ID = Order.CustomerID。 - RBarryYoung

Answer 1

就结果集和查询计划而言，这两种方法可能会产生完全相同的结果。

在这方面，它们是一样的。

如果谈到可读性，我会使用 WHERE 版本，因为连接条件是明确的，WHERE 子句也明确指定了要包含/排除的结果。

- Oded

---

这是不正确的：第一个查询会返回所有没有订单的客户。第二个查询会返回所有客户。 - user662852

@user662852 - 你在说什么？第二个查询也受到“AND CustomerID IS NULL”的限制。 - Oded

除非您使用“set ansi_nulls off”，否则order.customerid无法等于customer.id的任何值并且为null，因此on条件始终为false；左连接返回所有未加入订单记录的客户。 - user662852

Answer 2

我会尽力用自己的方式来解释：

考虑下面的表格：

tblQuestions：

QuestionId                   1                    2
QuestionTitle                Your Name?           Your Age?

tblPersons：

PersonId                     1                    2
PersonName                   Person1              Person2

tblAnswers:

AnswerId                     1
PersonId                     1
QuestionId                   1
Answer                       My Name is Person1

现在使用WHERE子句尝试此查询，它将仅返回一个结果。

SELECT  q.QuestionId, q.QuestionName, a.Answer
FROM    tblQuestions q
        LEFT OUTER JOIN tblAnswers a ON q.QuestionId = a.QuestionId
WHERE   a.PersonId = 2 OR a.PersonId IS null

现在使用ON关键字尝试此查询，它将返回两个结果。

SELECT  q.QuestionId, q.QuestionName, a.Answer 
FROM    tblQuestions q 
        LEFT OUTER JOIN tblAnswers a ON q.QuestionId = a.QuestionId AND
    (a.PersonId = 2 OR a.PersonId IS null)

结果的差异是由于筛选器PersonId = 1 OR PersonId Is NULL，当该筛选器通过WHERE应用时，结果为一条记录，而当它在`ON'中应用时，结果为两条记录。

- Yaqub Ahmad

Answer 3

如果你只考虑结果，那么两者将返回相同的结果。但是它们之间确实存在一些差异。第一个查询：

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.ID 
WHERE CustomerID IS NULL

首先将应用连接获取记录，然后在其上应用过滤器（Where）条件。

第二个查询，

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.ID AND
      CustomerID IS NULL

在这里，您加入时即可获得所需的结果。就性能而言，这将是不错的选择。

希望这有些意义！！

- Mariappan Subramanian

---

认为查询的不同部分按特定顺序运行是过于简单化的 - 查询规划器可能不会按照您的期望进行选择。 - Oded

是的，但这就是发生的事情。显然，当您连接两个表时，它将根据连接条件获取结果，并应用进一步的连接条件！！ - Mariappan Subramanian

你自相矛盾。 - Oded

这取决于数据和统计数据。查询规划器将根据统计信息计划执行 - 无法事先确定它会选择什么。 - Oded

第一个查询显示所有没有订单的客户。第二个查询显示所有客户。 - user662852

Answer 4

第一个案例将返回满足条件的结果。

而第二个案例将返回在两个条件下连接的结果。

- Nipun Jain

---

在两个查询中，是否存在结果可能不同的情况？ - w0051977

Answer 5

所选答案不正确。

第一个查询返回所有没有订单的客户。 第二个查询返回所有客户。

在第一个查询中，具有订单的客户加入其订单。 由于左连接而存在没有订单的客户。 然后应用Where筛选 - 具有订单的客户被过滤掉，因为CustomerID不为null； 没有订单的客户仍然存在。

在第二个查询中，无论您放入什么数据，都不能加入记录，因此所有没有订单的客户都存在。 这两者都可以为真的联接条件不存在（吹毛求疵：除非您具有空ID的客户记录和空ID的订单记录，并使用“SET ANSI_NULLS OFF”声明）：“Customer.ID = Order.ID AND CustomerID IS NULL”。

- user662852