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 INis supported.NOT INThere can only be single columns on the left, not multiple columns.The
NULLvalue cannot appear in the list to the right ofNOT IN.