sql - 为什么此语句仅适用于WHERE？

Question

我有一个客户表和一个订单表。并非所有客户都下订单，因此并非所有客户ID都在订单表上。我希望我的结果仅显示未下订单的客户表中的值，因此orderID列应显示为null。以下代码有效:

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM Customers c
    LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID WHERE o.OrderID IS NULL

但是我最初尝试过的却没有:

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM Customers c
    LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID AND o.OrderID IS NULL

而是显示此客户表上的所有值，但所有的orderID都为null

我不认为我真正了解其中的区别，因为我觉得他们俩都返回相同的东西是有意义的。

Answer 1

这是外部联接(在这种情况下为左联接)的性质。左联接将获取您的主表(客户)，并通过条件将其与联接表(订单)进行匹配。对于客户中没有匹配项的每一行，与内部联接不同，它不会删除该行。相反，它会添加“订单”中的所有字段，但会在其中添加null。

看这个例子:

表A表B
┐──────┬──────┐┌──────┬──────┐
│field1│field2││field3│field4│
├──────┼──────┤├──────┼──────┤
│A│1││1│一个│
│B│2││3│三│
│C│3│└──────┴──────┘
└──────┴──────

表的内部联接(在field2和field3之间)为:

──────┬──────┬────┬
│field1│field2│field3│field4│
├──────┼──────┤────┼
│A│1│1│一个│
│C│3│3│三│
──────┴──────┴────┴

但是表的外部联接必须为您提供每条记录，如果没有匹配项，则将其替换为null。

──────┬──────┬────┬
│field1│field2│field3│field4│
├──────┼──────┤────┼
│A│1│1│一个│
│B│2│NULL│NULL│⬅︎无匹配项
│C│3│3│三│
──────┴──────┴────┴

现在，如果表2中根本没有匹配项，将会发生什么？例如，是否在ON子句中添加了不可能的条件？然后，结果中的所有记录将看起来像“不匹配”

──────┬──────┬────┬
│field1│field2│field3│field4│
├──────┼──────┤────┼
│A│1│NULL│NULL│⬅︎无匹配(由于不可能的条件)
│B│2│NULL│NULL│⬅︎无匹配(由于不可能的条件)
│C│3│NULL│NULL│⬅︎无匹配(由于不可能的条件)
──────┴──────┴────┴

因此，是否没有匹配是因为表2中没有给定ID的记录，还是因为您添加了不可能的条件而没有匹配，都没有关系。外部联接的结果是，原本应该来自Table2的字段将被替换为null。因为这就是定义外部联接的方式。

现在到现实世界的表:

在OrderID为null的Order中实际上没有任何记录(除非您设计得很糟糕)。因此，如果将该条件放在ON子句中，它将找不到符合您条件的记录。

在这种情况下，由于这是外部(左侧)联接，因此您将获得所有原始的客户记录，并且由于没有匹配项，因此每个记录都有“订单”中的字段均为空。

在将条件放入WHERE的情况下，您实际上是在充分利用左联接的这种行为。您正在根据每个客户的订单进行匹配。如果匹配-很好，您得到了实际的订单ID。但是，如果没有匹配项-您要寻找的匹配项-它会添加一个空订单ID。

然后，where子句告诉它仅向您提供发生该位置的记录。也就是说，在Orders中没有匹配顺序的记录。