MySQL IN 和 NOT IN 运算符

 一、基本语法与功能

1. IN 运算符

expr IN (value1, value2, ..., valueN)

 当 expr 等于列表中任意一个值时返回 1 (TRUE)

 否则返回 0 (FALSE)

 列表中有 NULL 且无匹配时返回 NULL

2. NOT IN 运算符

expr NOT IN (value1, value2, ..., valueN)

 当 expr 不等于列表中所有值时返回 1 (TRUE)

 否则返回 0 (FALSE)

 列表中有 NULL 且无匹配时返回 NULL

二、使用示例

基础用法

数字判断

SELECT 2 IN (1,3,5), 2 NOT IN (1,3,5); --结果: 0, 1

字符串判断

SELECT 'apple' IN ('orange', 'banana', 'apple'); --结果: 1

混合类型(MySQL会尝试类型转换)

SELECT 2 IN ('1','2','3'); --结果: 1 (字符串'2'转换为数字2)

实际查询应用

查找特定ID的员工

SELECT * FROM employees WHERE id IN (101, 205, 307);

查找不在特定部门的员工

SELECT * FROM employees WHERE department NOT IN ('HR', 'Finance');

三、NULL 值处理规则

IN 运算符的 NULL 处理

NOT IN 运算符的 NULL 处理

四、性能优化建议

1. 大列表处理：

    对于大型值列表(如上千个值)，考虑使用临时表连接替代

   CREATE TEMPORARY TABLE temp_values (val INT);
   INSERT INTO temp_values VALUES (1),(3),(5),(7);
   SELECT * FROM main_table JOIN temp_values ON main_table.id = temp_values.val;

2. 索引利用：

    IN 列表中的值较多时，MySQL 可能无法有效使用索引

    少量离散值使用 IN，连续范围使用 BETWEEN

3. 替代方案：

    某些情况下 EXISTS 可能更高效

SELECT * FROM table1 
   WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id);

五、特殊用法

1. 子查询结合：

查找有订单的客户

SELECT * FROM customers 
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

2. 多列比较：

 查找特定(城市,州)组合的员工

SELECT * FROM employees 
WHERE (city, state) IN (('Boston','MA'), ('Chicago','IL'));

3. NOT IN 的替代写法：

 以下两种写法等价

SELECT * FROM products WHERE category NOT IN (1,2,3);
SELECT * FROM products WHERE NOT category IN (1,2,3);

六、注意事项

1.类型转换规则：

   MySQL 会尝试将列表值转换为表达式类型

    避免混合不兼容类型，如 `'abc' IN (1,2,3)`

2. 性能陷阱：

    `NOT IN` 对包含 NULL 的列表可能返回意外结果

    考虑使用 `NOT EXISTS` 替代

3. 与 OR 的等价关系：

    `expr IN (a,b,c)` 等价于 `expr = a OR expr = b OR expr = c`

    但 IN 通常更高效且易读