mysql join is null_在连接条件上使用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

结果是，左表中的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 |

+------+--------+------+-------+

2020-05-17