在连接条件上使用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 NULLLEFT 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

所在的行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 |

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

以上是 在连接条件上使用IS NULL或IS NOT NULL-理论问题 的全部内容, 来源链接: utcz.com/qa/429734.html

回到顶部