NOT IN

Grammar Description

The NOT IN operator can specify multiple specific values in the WHERE statement, essentially abbreviation for multiple XOR conditions.

Grammar Structure

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

Example

create table t2(a int,b varchar(5),c float, d date, e datetime);
insert into t2 values(1,'a',1.001,'2022-02-08','2022-02-08 12:00:00');
insert into t2 values(2,'b',2.001,'2022-02-09','2022-02-09 12:00:00');
insert into t2 values(1,'c',3.001,'2022-02-10','2022-02-10 12:00:00');
insert into t2 values(4,'d',4.001,'2022-02-11','2022-02-11 12:00:00');

mysql> select * from t2 where a not in (2,4);
+------+------+----------------------------------------------------------------------------------------------------------------
| a | b | c | d | e |
+------+------+----------------------------------------------------------------------------------------------------------------
| 1 | a | 1.001 | 2022-02-08 | 2022-02-08 12:00:00 |
| 1 | c | 3.001 | 2022-02-10 | 2022-02-10 12:00:00 |
+------+------+----------------------------------------------------------------------------------------------------------------
2 rows in set (0.05 sec)

mysql> select * from t2 where b not in ('e',"f");
+------+------+----------------------------------------------------------------------------------------------------------------
| a | b | c | d | e |
+------+------+----------------------------------------------------------------------------------------------------------------
| 1 | a | 1.001 | 2022-02-08 | 2022-02-08 12:00:00 |
| 2 | b | 2.001 | 2022-02-09 | 2022-02-09 12:00:00 |
| 1 | c | 3.001 | 2022-02-10 | 2022-02-10 12:00:00 |
| 4 | d | 4.001 | 2022-02-11 | 2022-02-11 12:00:00 |
+------+------+----------------------------------------------------------------------------------------------------------------
4 rows in set (0.09 sec)

mysql> select * from t2 where e not in ('2022-02-09 12:00:00') and a in (4,5);
+------+------+----------------------------------------------------------------------------------------------------------------
| a | b | c | d | e |
+------+------+----------------------------------------------------------------------------------------------------------------
| 4 | d | 4.001 | 2022-02-11 | 2022-02-11 12:00:00 |
+------+------+----------------------------------------------------------------------------------------------------------------
1 row in set (0.05 sec)

limit

  • Currently only the list of constants to the left of NOT IN is supported.

  • NOT IN There can only be single columns on the left, not multiple columns.

  • The NULL value cannot appear in the list to the right of NOT IN.

Last updated on