mysql - IN operator caluse 具有 NULL 值似乎有效

Question

我有一个 employee 表:

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| 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 值。

如果 IN 运算符等同于 field=val1 OR field=val2 OR field=val3 OR field=null 为什么这个查询不会失败或给出一些错误？

Answer 1

它工作得很好。您可能会混淆 in 和 not in。

如果您运行not in，那么您将得不到任何结果。为什么？好吧，考虑 1 不在 (2, 3) 中。这评估为真。不费吹灰之力。

那么 2 不在 (2, 3) 中。这评估为假。正如它应该的那样。

但是。 . .这两个怎么样:

• 2 不在 (2, 3, NULL) 中
• 1 不在 (2, 3, NULL) 中

第一个是假的，因为“2”确实在列表中。第二个是。 . .好吧，NULL 不是一个值。意思是“未知”。所以，它可能是“1”或其他。因此，它的计算结果为 NULL。并且 NULL 在 where 中被视为与 false 相同。

你可以计算出来，但这不会发生在 in 中。例如，在您的情况下，NULL or 1=1 的计算结果为 true。

出于这个原因，我强烈建议始终对子查询使用 not exists 而不是 not in。一个推论是我建议使用 exists 而不是 in，但这样使用 exists 的习惯就根深蒂固了。