在连接条件上使用IS NULL或IS NOT NULL-理论问题

Question

这里的理论问题：

为什么指定table.field IS NULL或table.field IS NOT
NULL不能在联接条件（例如，左联接或右联接）上起作用，而仅在where条件下起作用？

非工作示例：

-这应该返回所有发货，并过滤掉所有退货（非空值）。但是，这将返回所有装运，无论是否满足[r.id为null]语句。

SELECT
  *
FROM 
  shipments s
LEFT OUTER JOIN returns r  
  ON s.id = r.id
  AND r.id is null
WHERE
  s.day >= CURDATE() - INTERVAL 10 DAY

工作示例：

-这将返回正确的行数，即总发货量，减去与退货相关的所有行（非空值）。

SELECT
  *
FROM 
  shipments s
LEFT OUTER JOIN returns r  
  ON s.id = r.id
WHERE
  s.day >= CURDATE() - INTERVAL 10 DAY
  AND r.id is null

为什么会这样呢？被联接的两个表之间的所有其他过滤条件都可以正常工作，但是由于某些原因，除非在where语句中，否则IS NULL和IS NOT
NULL过滤器将不起作用。

这是什么原因呢？

Answer 1

表A和B的示例：

 A (parent)       B (child)    
============    =============
 id | name        pid | name 
------------    -------------
  1 | Alex         1  | Kate
  2 | Bill         1  | Lia
  3 | Cath         3  | Mary
  4 | Dale       NULL | Pan
  5 | Evan

如果要查找父母及其子女，请执行以下操作INNER JOIN：

SELECT id,  parent.name AS parent
     , pid, child.name  AS child

FROM
        parent  INNER JOIN  child
  ON   parent.id     =    child.pid

结果是，左表中的s 与第二个表中parent的s的每个匹配项都将在结果中显示为一行：id``child``pid

+----+--------+------+-------+
| id | parent | pid  | child | 
+----+--------+------+-------+
|  1 | Alex   |   1  | Kate  |
|  1 | Alex   |   1  | Lia   |
|  3 | Cath   |   3  | Mary  |
+----+--------+------+-------+

现在，上面没有显示没有孩子的父母（因为他们的ID与孩子的ID不匹配，所以您要怎么做？您改为进行外部联接。外部联接有三种类型，左联接，右联接和完整的外部联接，我们需要左边的联接，因为我们想要左边表（父表）中的“额外”行：

SELECT id,  parent.name AS parent
     , pid, child.name  AS child

FROM
        parent  LEFT JOIN  child
  ON   parent.id    =    child.pid

结果是，除了以前的比赛之外，还会显示所有没有比赛的父母（读：没有孩子）：

+----+--------+------+-------+
| id | parent | pid  | child | 
+----+--------+------+-------+
|  1 | Alex   |   1  | Kate  |
|  1 | Alex   |   1  | Lia   |
|  3 | Cath   |   3  | Mary  |
|  2 | Bill   | NULL | NULL  |
|  4 | Dale   | NULL | NULL  |
|  5 | Evan   | NULL | NULL  |
+----+--------+------+-------+

这些NULL都是从哪里来的？那么，MySQL的（或任何其他RDBMS，你可以使用），不知道该怎么把那里作为这些父母没有匹配（孩子），所以没有pid也不会child.name以配合这些父母。因此，它将这个特殊的非值称为NULL。

我的观点是，这些NULLs是在期间创建的（在结果集中）LEFT OUTER JOIN。

因此，如果我们只想显示没有孩子的父母，则可以WHERE child.pid IS NULL在LEFT JOIN上面添加一个。完成
后将评估（检查） 该WHERE子句JOIN。因此，从上面的结果可以清楚地看到，只有最后三行（其中pidNULL为）将显示：

SELECT id,  parent.name AS parent
     , pid, child.name  AS child

FROM
        parent  LEFT JOIN  child
  ON   parent.id    =    child.pid

WHERE child.pid IS NULL

结果：

+----+--------+------+-------+
| id | parent | pid  | child | 
+----+--------+------+-------+
|  2 | Bill   | NULL | NULL  |
|  4 | Dale   | NULL | NULL  |
|  5 | Evan   | NULL | NULL  |
+----+--------+------+-------+

现在，如果我们将IS NULL检查从WHERE移到加入ON子句，会发生什么？

SELECT id,  parent.name AS parent
     , pid, child.name  AS child

FROM
        parent  LEFT JOIN  child
  ON   parent.id    =    child.pid
  AND  child.pid IS NULL

在这种情况下，数据库尝试从两个表中找到符合这些条件的行。也就是说，其中parent.id = child.pid AND
所在的行child.pid IN NULL。但是它找不到 这样的匹配项， 因为no
child.pid不能等于某个值（1、2、3、4或5）并且同时为NULL！

因此，条件：

ON   parent.id    =    child.pid
AND  child.pid IS NULL

等效于：

ON   1 = 0

总是这样False。

那么，为什么它返回左表中的所有行？ 因为这是左联接！ 左联接返回 匹配的行（在这种情况下为无） ，也返回 左表中与 检查
不匹配 的行（ 在本例中为全部 ）：

+----+--------+------+-------+
| id | parent | pid  | child | 
+----+--------+------+-------+
|  1 | Alex   | NULL | NULL  |
|  2 | Bill   | NULL | NULL  |
|  3 | Cath   | NULL | NULL  |
|  4 | Dale   | NULL | NULL  |
|  5 | Evan   | NULL | NULL  |
+----+--------+------+-------+

我希望以上解释清楚。

旁注（与您的问题没有直接关系）：为什么Pan我们的JOIN都没有出现？由于在SQL（非常见）逻辑中，他的pidis
NULL和NULL不等于任何值，因此它不能与任何父ID（即1,2,3,4和5）匹配。即使那里有NULL，它也不会匹配，因为NULL它不等于任何东西，甚至不等于NULL本身（确实是一个非常奇怪的逻辑！）。这就是为什么我们使用特殊支票IS NULL而不是= NULL支票的原因。

那么，Pan如果我们做一个会出现RIGHT JOIN吗？是的，它会的！因为RIGHT JOIN将显示所有匹配的结果（我们做的第一个INNER
JOIN）以及RIGHT表中所有不匹配的行（在我们的例子中是(NULL, 'Pan')行）。

SELECT id,  parent.name AS parent
     , pid, child.name  AS child

FROM
        parent  RIGHT JOIN  child
  ON   parent.id     =    child.pid

结果：

+------+--------+------+-------+
| id   | parent | pid  | child | 
+---------------+------+-------+
|   1  | Alex   |   1  | Kate  |
|   1  | Alex   |   1  | Lia   |
|   3  | Cath   |   3  | Mary  |
| NULL | NULL   | NULL | Pan   |
+------+--------+------+-------+

不幸的是，MySQL没有FULL JOIN。您可以在其他RDBMS中尝试它，它将显示：

+------+--------+------+-------+
|  id  | parent | pid  | child | 
+------+--------+------+-------+
|   1  | Alex   |   1  | Kate  |
|   1  | Alex   |   1  | Lia   |
|   3  | Cath   |   3  | Mary  |
|   2  | Bill   | NULL | NULL  |
|   4  | Dale   | NULL | NULL  |
|   5  | Evan   | NULL | NULL  |
| NULL | NULL   | NULL | Pan   |
+------+--------+------+-------+