在连接条件上使用IS NULL或IS NOT NULL-理论问题
这里的理论问题:
为什么指定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过滤器将不起作用。
这是什么原因呢?
回答:
表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
。
因此,如果我们只想显示没有孩子的父母,则可以WHERE child.pid IS NULL
在LEFT JOIN
上面添加一个。完成
。因此,从上面的结果可以清楚地看到,只有最后三行(其中pid
NULL为)将显示:
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
所在的行child.pid IN NULL
。但是它找不到 因为no
child.pid
不能等于某个值(1、2、3、4或5)并且同时为NULL!
因此,条件:
ON parent.id = child.pidAND 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(非常见)逻辑中,他的pid
is
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 |
+------+--------+------+-------+
以上是 在连接条件上使用IS NULL或IS NOT NULL-理论问题 的全部内容, 来源链接: utcz.com/qa/429734.html