IN
Grammar Description
The IN operator can specify multiple specific values in the WHERE statement, essentially abbreviation for multiple OR conditions.
Grammar Structure
> SELECT column1, column2, ...
FROM table_name
WHERE column_name 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 in (2,4);
+------+------+----------------------------------------------------------------------------------------------------------------
| a | b | c | d | e |
+------+------+----------------------------------------------------------------------------------------------------------------
| 2 | b | 2.001 | 2022-02-09 | 2022-02-09 12:00:00 |
| 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 |
+------+------+----------------------------------------------------------------------------------------------------------------
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 |
+------+------+----------------------------------------------------------------------------------------------------------------
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 |
+------+------+----------------------------------------------------------------------------------------------------------------
limit
- Currently, only constant lists are supported on the left side of
IN. INonly supports single column data on the left, and does not support tuples composed of multiple columns.INhas not processed theNULLvalue very well, and theNULLvalue cannot be used on the right side.