在连接条件上使用IS NULL或IS NOT NULL-理论问题 [英] Using IS NULL or IS NOT NULL on join conditions - Theory question
问题描述
这里的理论问题:
为什么指定table.field IS NULL或table.field IS NOT NULL不能在联接条件(例如，左联接或右联接)上起作用，而仅在where条件下起作用?
Why does specifying table.field IS NULL or table.field IS NOT NULL not work on a join condition (left or right join for instance) but only in the where condition?
非工作示例:
-这应该退回所有发货，并过滤掉所有退货(非空值).但是，无论是否满足[r.id为null]语句，此操作都会返回所有货件.
-this should return all shipments with any returns (non null values) filtered out. However, this returns all shipments regardless if anything meets the [r.id is null] statement.
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
工作示例:
-这将返回正确的行数，即总出货量，减去与退货相关的所有行(非空值).
-This returns the correct amount of rows which is total shipments, less any related to a returns (non null values).
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过滤器将不起作用.
Why is this the case? All other filter conditions between two tables being joined work just fine, but for some reason IS NULL and IS NOT NULL filters do not work unless in the where statement.
这是什么原因?
推荐答案
表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的id和第二张表中的child的pid的每个匹配项都将在结果中显示为一行:
Result is that every match of a parent's id from the left table and a child's pid from the second table will show as a row in the result:
+----+--------+------+-------+
| id | parent | pid | child |
+----+--------+------+-------+
| 1 | Alex | 1 | Kate |
| 1 | Alex | 1 | Lia |
| 3 | Cath | 3 | Mary |
+----+--------+------+-------+
现在，上面没有显示没有孩子的父母(因为他们的ID与孩子的ID不匹配，所以您要怎么做?您要进行外部联接.外部联接有三种类型，左侧，右边和完整的外部联接.我们需要左边的联接，因为我们想要左边表(父表)中的额外"行:
Now, the above does not show parents without kids (because their ids do not have a match in child's ids, so what do you do? You do an outer join instead. There are three types of outer joins, the left, the right and the full outer join. We need the left one as we want the "extra" rows from the left table (parent):
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent LEFT JOIN child
ON parent.id = child.pid
结果是，除了以前的比赛以外，还会显示所有没有比赛的父母(读:没有孩子):
Result is that besides previous matches, all parents that do not have a match (read: do not have a kid) are shown too:
+----+--------+------+-------+
| 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 did all those NULL come from? Well, MySQL (or any other RDBMS you may use) will not know what to put there as these parents have no match (kid), so there is no pid nor child.name to match with those parents. So, it puts this special non-value called NULL.
我的意思是，这些NULLs是在LEFT OUTER JOIN期间创建的(在结果集中).
My point is that these NULLs are created (in the result set) during the LEFT OUTER JOIN.
因此，如果我们只想显示没有孩子的父母，则可以在上面的LEFT JOIN中添加WHERE child.pid IS NULL. 完成JOIN 后，将评估(选中)WHERE子句.因此，从以上结果很明显，仅显示pid为NULL的最后三行:
So, if we want to show only the parents that do NOT have a kid, we can add a WHERE child.pid IS NULL to the LEFT JOIN above. The WHERE clause is evaluated (checked) after the JOIN is done. So, it's clear from the above result that only the last three rows where the pid is NULL will be shown:
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子句，会发生什么?
Now, what happens if we move that IS NULL check from the WHERE to the joining ON clause?
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所在的行.但是它找不到没有这样的匹配，因为没有child.pid可以等于(1、2、3、4或5)并且同时为NULL！
In this case the database tries to find rows from the two tables that match these conditions. That is, rows where parent.id = child.pid AND child.pid IN NULL. But it can find no such match because no child.pid can be equal to something (1, 2, 3, 4 or 5) and be NULL at the same time!
所以，条件:
ON parent.id = child.pid
AND child.pid IS NULL
等效于:
ON 1 = 0
始终为False.
那么，为什么它返回左表中的所有行? 因为这是一个左联接！，并且左联接返回匹配的行(在这种情况下为无)，还返回左表中的不匹配的行 >支票(在这种情况下，全部为 ):
So, why does it return ALL rows from the left table? Because it's a LEFT JOIN! And left joins return rows that match (none in this case) and also rows from the left table that do not match the check (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 |
+----+--------+------+-------+
我希望上面的解释清楚.
I hope the above explanation is clear.
旁注(与您的问题没有直接关系):为什么Pan在我们的所有JOIN中都没有出现?因为他的pid是NULL，并且在SQL(非常见)逻辑中NULL不等于任何值，所以它不能与任何父代ID(1、2、3、4和5)匹配. .即使那里有一个NULL，它也不会匹配，因为NULL不等于任何东西，甚至不等于NULL本身(确实是一个非常奇怪的逻辑！).这就是为什么我们使用特殊检查IS NULL而不是= NULL检查的原因.
Sidenote (not directly related to your question): Why on earth doesn't Pan show up in none of our JOINs? Because his pid is NULL and NULL in the (not common) logic of SQL is not equal to anything so it can't match with any of the parent ids (which are 1,2,3,4 and 5). Even if there was a NULL there, it still wouldn't match because NULL does not equal anything, not even NULL itself (it's a very strange logic, indeed!). That's why we use the special check IS NULL and not a = NULL check.
那么，如果我们执行RIGHT JOIN，会显示Pan吗?是的，它会的！因为RIGHT JOIN将显示所有匹配的结果(我们做的第一个INNER JOIN)以及RIGHT表中所有不匹配的行(在我们的示例中为(NULL, 'Pan')行).
So, will Pan show up if we do a RIGHT JOIN ? Yes, it will! Because a RIGHT JOIN will show all results that match (the first INNER JOIN we did) plus all rows from the RIGHT table that don't match (which in our case is one, the (NULL, 'Pan') row.
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中尝试它，它将显示:
Unfortunately, MySQL does not have FULL JOIN. You can try it in other RDBMSs, and it will show:
+------+--------+------+-------+
| 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-理论问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
