NOT IN

语法说明

NOT IN 运算符可以在 WHERE 语句中指定特定的多个值，本质上是多个 XOR 条件的简写。

语法结构

  1. > SELECT column1, column2, ...
  2. FROM table_name
  3. WHERE column_name NOT IN (value1, value2, ...);

示例

  1. create table t2(a int,b varchar(5),c float, d date, e datetime);
  2. insert into t2 values(1,'a',1.001,'2022-02-08','2022-02-08 12:00:00');
  3. insert into t2 values(2,'b',2.001,'2022-02-09','2022-02-09 12:00:00');
  4. insert into t2 values(1,'c',3.001,'2022-02-10','2022-02-10 12:00:00');
  5. insert into t2 values(4,'d',4.001,'2022-02-11','2022-02-11 12:00:00');
  7. mysql> select * from t2 where a not in (2,4);
  8. +------+------+-------+------------+----------------------------+
  9. | a    | b    | c     | d          | e                          |
  10. +------+------+-------+------------+----------------------------+
  11. |    1 | a    | 1.001 | 2022-02-08 | 2022-02-08 12:00:00.000000 |
  12. |    1 | c    | 3.001 | 2022-02-10 | 2022-02-10 12:00:00.000000 |
  13. +------+------+-------+------------+----------------------------+
  14. 2 rows in set (0.00 sec)
  16. mysql> select * from t2 where b not in ('e',"f");
  17. +------+------+-------+------------+----------------------------+
  18. | a    | b    | c     | d          | e                          |
  19. +------+------+-------+------------+----------------------------+
  20. |    1 | a    | 1.001 | 2022-02-08 | 2022-02-08 12:00:00.000000 |
  21. |    2 | b    | 2.001 | 2022-02-09 | 2022-02-09 12:00:00.000000 |
  22. |    1 | c    | 3.001 | 2022-02-10 | 2022-02-10 12:00:00.000000 |
  23. |    4 | d    | 4.001 | 2022-02-11 | 2022-02-11 12:00:00.000000 |
  24. +------+------+-------+------------+----------------------------+
  25. 4 rows in set (0.01 sec)
  27. mysql> select * from t2 where e not in ('2022-02-09 12:00:00') and a in (4,5);
  28. a   b   c   d   e
  29. 4   d   4.0010  2022-02-11  2022-02-11 12:00:00

限制

  • 目前只支持 NOT IN 左侧的常量列表。
  • NOT IN 左边只能有单列，不能是多列。
  • NULL 值不能出现在 NOT IN 右侧的列表中。