mysql - SQL IN () 包含 NULL 值

Question

员工表:

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname    | lname     | start_date | end_date | superior_emp_id | dept_id | title              | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
|      1 | Michael  | Smith     | 2005-06-22 | NULL     |            NULL |       3 | President          |                  1 |
|      2 | Susan    | Barker    | 2006-09-12 | NULL     |               1 |       3 | Vice President     |                  1 |
|      3 | Robert   | Tyler     | 2005-02-09 | NULL     |               1 |       3 | Treasurer          |                  1 |
|      4 | Susan    | Hawthorne | 2006-04-24 | NULL     |               3 |       1 | Operations Manager |                  1 |
|      5 | John     | Gooding   | 2007-11-14 | NULL     |               4 |       2 | Loan Manager       |                  1 |
|      6 | Helen    | Fleming   | 2008-03-17 | NULL     |               4 |       1 | Head Teller        |                  1 |
|      7 | Chris    | Tucker    | 2008-09-15 | NULL     |               6 |       1 | Teller             |                  1 |
|      8 | Sarah    | Parker    | 2006-12-02 | NULL     |               6 |       1 | Teller             |                  1 |
|      9 | Jane     | Grossman  | 2006-05-03 | NULL     |               6 |       1 | Teller             |                  1 |
|     10 | Paula    | Roberts   | 2006-07-27 | NULL     |               4 |       1 | Head Teller        |                  2 |
|     11 | Thomas   | Ziegler   | 2004-10-23 | NULL     |              10 |       1 | Teller             |                  2 |
|     12 | Samantha | Jameson   | 2007-01-08 | NULL     |              10 |       1 | Teller             |                  2 |
|     13 | John     | Blake     | 2004-05-11 | NULL     |               4 |       1 | Head Teller        |                  3 |
|     14 | Cindy    | Mason     | 2006-08-09 | NULL     |              13 |       1 | Teller             |                  3 |
|     15 | Frank    | Portman   | 2007-04-01 | NULL     |              13 |       1 | Teller             |                  3 |
|     16 | Theresa  | Markham   | 2005-03-15 | NULL     |               4 |       1 | Head Teller        |                  4 |
|     17 | Beth     | Fowler    | 2006-06-29 | NULL     |              16 |       1 | Teller             |                  4 |
|     18 | Rick     | Tulman    | 2006-12-12 | NULL     |              16 |       1 | Teller             |                  4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+

查询:

SELECT emp_id, fname, lname, title
FROM employee
WHERE emp_id IN (SELECT superior_emp_id FROM employee);

查询结果:

+--------+---------+-----------+--------------------+
| emp_id | fname   | lname     | title              |
+--------+---------+-----------+--------------------+
|      1 | Michael | Smith     | President          |
|      3 | Robert  | Tyler     | Treasurer          |
|      4 | Susan   | Hawthorne | Operations Manager |
|      6 | Helen   | Fleming   | Head Teller        |
|     10 | Paula   | Roberts   | Head Teller        |
|     13 | John    | Blake     | Head Teller        |
|     16 | Theresa | Markham   | Head Teller        |
+--------+---------+-----------+--------------------+

子查询结果:

+-----------------+
| superior_emp_id |
+-----------------+
|            NULL |
|               1 |
|               1 |
|               3 |
|               4 |
|               4 |
|               4 |
|               4 |
|               4 |
|               6 |
|               6 |
|               6 |
|              10 |
|              10 |
|              13 |
|              13 |
|              16 |
|              16 |
+-----------------+

如果子查询 SELECT superior_emp_id FROM employee 为 Michael Smith 返回 NULL，IN() 运算符如何在最终结果集中返回它？我认为 nothing 不等于 null。

Answer 1

If the subquery SELECT superior_emp_id FROM employee returns NULL for Michael Smith how is it that the IN() operator returns it in the final result set?

简短的回答，它没有。

子查询有效地返回整组 superior_emp_ids [NULL, 1, 1, 3, 3, 4, 4, 6, 6, 6, 4, 10, 10, 4, 13, 13, 4, 16, 16] 每行。

您的 WHERE 子句测试每个 emp_id 以查看它是否在此集合中。 IN 基本上是一系列相等的比较或运算。

Michael 的 emp_id 是 1 并且返回他的行是因为 1 = NULL OR 1 = 1 .... 可以写成 FALSE OR TRUE ....返回 TRUE。

你假设 NULL 不等于任何东西(包括 NULL)是正确的，所以 WHERE NULL IN (NULL, 1, FALSE, ... anything you like ...) 将返回 FALSE .但这不是您的示例中发生的情况。

注意为避免混淆，最好尽可能避免在 IN 子句的任何一侧使用 NULL 记录，如@Donal

所引用