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

表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

结果是，左表中的parent's id和第二表中的child's 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)将不知道该放在那里什么，因为这些父母没有匹配项(kid)，所以没有pid或child.name可与这些父母匹配.所以，它把这个特殊的非值称为NULL.

My point is that these 100 are created (in the result set) during the 101.

所以，如果我们只想显示没有子元素的父母，我们可以在上面的LEFT JOIN加上WHERE child.pid IS NULL.The 102 clause is evaluated (checked) after the 103完成了.因此，从上面的结果可以清楚地看出，只有pid为空的最后三行才会显示:

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

在这种情况下，数据库try 从两个表中查找符合这些条件的行.也就是parent.id = child.pid AND child.pid IN NULL所在的行.但它可以找到no such match，因为child.pid可以等于某个值(1、2、3、4或5)，同时为空！

所以，条件是:

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

相当于:

ON   1 = 0

总是False.

那么，为什么它会返回左表中的所有行呢？Because it's a LEFT JOIN!和left连接返回rows that match (none in this case)和rows from the left table that do not match支票(all in this case):

+----+--------+------+-------+
| 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在我们的加入中都没有出现？因为他的pid是NULL，在SQL的(非公共)逻辑中NULL不等于任何值，所以它不能与任何父ID(1、2、3、4和5)匹配.即使有一个空值，它仍然不会匹配，因为NULL不等于任何东西，甚至NULL本身也不等于任何东西(这的确是一个非常奇怪的逻辑！).这就是为什么我们使用IS NULL号特别支票而不是= NULL号支票.

如果我们做RIGHT JOIN，Pan会出现吗？是的，会的！因为右连接将显示所有匹配的结果(我们做的第一个内部连接)加上右表中所有不匹配的行(在我们的例子中是一行，(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中try ，它将显示:

+------+--------+------+-------+
|  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   |
+------+--------+------+-------+