SQL IN 用法完全指南：从基础语法到性能优化与 NULL 陷阱

如果你写 SQL 已经有段时间一定遇到过这种场景想查某个城市的所有用户条件里要匹配“北京、上海、广州、深圳”四个值。新手第一反应是写四个OR老手会顺手写一个IN。但IN真的只是“多个 OR 的简写”吗如果你这么想那今天这篇文章值得看完。因为IN看起来是 SQL 里最简单的语法之一实际使用中却藏着不少坑NULL值导致的诡异结果、NOT IN返回空表的经典陷阱、子查询中数据量过大引发的性能问题、以及IN列表拼接带来的 SQL 注入风险。这篇文章会从IN的核心语义讲起结合完整示例、性能对比、常见误区和排查思路帮你把这个“简单语法”真正用明白。无论你是刚学 SQL 的初学者还是在生产环境写复杂查询的开发者都能从中得到一些有用的判断依据。1. 为什么需要 IN从一段真实的查询需求说起先看一个最常见的开发场景。假设你维护一张用户表users需要统计华东区域核心城市的注册用户数。城市条件是“上海、杭州、南京、苏州、合肥”。没有IN的时候你只能这样写SELECT COUNT(*) FROM users WHERE city 上海 OR city 杭州 OR city 南京 OR city 苏州 OR city 合肥;这段 SQL 能跑但问题很明显条件一多SQL 冗长得像裹脚布。OR的优先级容易和AND混淆。比如你再加一个status 1的条件不加括号就可能出错。将来要调整城市列表你得改 SQL 里的每一行条件维护成本高。用IN改写之后同样逻辑变成SELECT COUNT(*) FROM users WHERE city IN (上海, 杭州, 南京, 苏州, 合肥);一句话总结IN解决的是“某个字段的值是否匹配集合中任意一个成员”的判断需求。它让 SQL 的表达式更接近自然语言可读性更高维护成本更低。但这里必须给一个明确判断IN不是简单的语法糖它的执行语义、边界行为、性能特征都和OR有区别。如果只把它当成“多个 OR 的缩写”遇到NULL、子查询、大数据量场景时就会踩坑。后面的章节会逐个拆解。2. IN 的核心概念与基础语法2.1 什么是 ININ是 SQL 中的条件运算符用于判断一个表达式的值是否包含在指定的值列表或子查询结果集中。它的结果是一个布尔值匹配成功返回TRUE匹配失败返回FALSE如果涉及NULL则可能返回UNKNOWN这部分是重点后面专门讲。基本语法有两种形式-- 形式一值列表 expression IN (value1, value2, value3, ...) -- 形式二子查询 expression IN (SELECT column FROM table WHERE condition)2.2 IN 的一个完整示例为了后续讲解方便先建一张简单的订单表模拟电商场景-- 文件路径demo.sql CREATE TABLE orders ( id INT PRIMARY KEY, customer_name VARCHAR(50), city VARCHAR(50), amount DECIMAL(10, 2), status VARCHAR(20) ); INSERT INTO orders (id, customer_name, city, amount, status) VALUES (1, 张三, 上海, 100.00, 已完成), (2, 李四, 杭州, 250.00, 待付款), (3, 王五, 南京, 80.50, 已完成), (4, 赵六, 北京, 320.00, 已取消), (5, 孙七, 广州, 150.00, 已完成), (6, 周八, 上海, 600.00, 待发货), (7, 吴九, 杭州, NULL, 已完成), (8, 郑十, 成都, 90.00, 已完成);查询华东城市上海、杭州、南京的订单SELECT id, customer_name, city, amount FROM orders WHERE city IN (上海, 杭州, 南京);运行结果idcustomer_namecityamount1张三上海100.002李四杭州250.003王五南京80.506周八上海600.007吴九杭州NULL注意第 7 行amount是NULL但city匹配IN条件所以记录正常返回。这说明IN只判断你写在条件里的列其他列的NULL值不影响行是否被选中。2.3 IN 的类型匹配规则IN后面的值列表必须与左侧表达式的数据类型兼容。比如city是字符串列表里就不能写数字。某些数据库如 MySQL在非严格模式下会做隐式转换但强烈建议不要依赖这种行为。-- 不推荐依赖隐式类型转换 SELECT * FROM orders WHERE city IN (1, 2, 3); -- 推荐类型保持一致 SELECT * FROM orders WHERE city IN (上海, 杭州);2.4 IN 对 NULL 的处理原则这是IN最容易踩坑的地方先记住两条结论IN列表中包含NULL不会匹配到任何“值为 NULL”的行。NOT IN的子查询或列表中存在NULL整个查询可能返回空结果。具体原因在 6.1 节详细展开。这里先看一个直观例子-- 查询城市为 NULL 的订单 SELECT * FROM orders WHERE city IN (NULL);这条 SQL 不会返回第 7 行那样的“城市为 NULL”的记录。因为在 SQL 的三值逻辑中NULL IN (NULL)的结果是UNKNOWN而WHERE子句只接受TRUE不接受TRUE以外的值。3. IN 与 OR、 ANY 的对比很多人想知道IN和OR到底有什么区别性能是否一样。答案取决于数据库实现和具体场景。3.1 IN 与 OR 的等价性如果列表中没有NULL且表达式不涉及子查询IN和OR在逻辑上是等价的WHERE city IN (上海, 杭州); -- 等价于 WHERE city 上海 OR city 杭州;从语义上这两条 SQL 返回相同的行。但从工程角度看IN的可读性和可维护性明显更好。3.2 与 AND 混用时的优先级坑OR的优先级低于AND多个条件混用时容易出错。比如要查“上海或杭州的已完成订单”-- 错误写法AND 优先级更高实际语义变成了“上海的全部订单 杭州的已完成订单” SELECT * FROM orders WHERE city 上海 OR city 杭州 AND status 已完成; -- 正确写法加括号 SELECT * FROM orders WHERE (city 上海 OR city 杭州) AND status 已完成; -- 更推荐写法用 IN 避免优先级问题 SELECT * FROM orders WHERE city IN (上海, 杭州) AND status 已完成;这个例子很好地说明IN不只是简化书写还能减少因为运算符优先级导致的逻辑错误。3.3 ANY 与 IN 的关系在 PostgreSQL 中IN实际上可以写成 ANYSELECT * FROM orders WHERE city ANY (ARRAY[上海, 杭州]);两者的执行计划通常是等价的。 ANY的写法更灵活因为数组可以动态传入IN的写法更直观。其他数据库也有各自的等价形式比如 MySQL 中可以用JSON_CONTAINS或FIND_IN_SET做类似事情但都不如IN直接。3.4 选择建议场景推荐写法原因固定值列表2~100 个值IN可读性好维护方便条件中包含AND且列表较长IN避免OR优先级问题值列表需要动态传入IN配合参数化查询或 ANY防止 SQL 注入子查询返回结果集IN或EXISTS视场景大数据量时需结合执行计划判断4. IN 在子查询中的使用与注意事项IN最常见的进阶用法是搭配子查询。比如查“下过单的用户”SELECT id, customer_name FROM users WHERE id IN (SELECT user_id FROM orders);这种写法逻辑清晰但有几个实际问题需要注意。4.1 子查询结果集大小如果子查询返回的数据量很大IN的性能可能成为问题。不同数据库的处理方式不同MySQL 对IN子查询做了优化但列表长度极大时仍可能产生临时表和额外开销。Oracle 对超过 1000 项的IN列表会直接报错ORA-01795。SQL Server 对IN列表长度也有限制虽然现代版本放宽了不少但超长列表依然是性能隐患。实际开发中如果子查询可能返回上万条数据通常建议改用EXISTS或JOIN。一个经验法则是外层表小、子查询大时用IN外层表大、子查询小时用EXISTS或JOIN。但这只是经验最终要看执行计划。4.2 IN 子查询的 NULL 陷阱NOT IN遇到子查询中包含NULL结果可能出现空表。这是数据库面试和实际开发中非常经典的一个坑。先看一个场景。orders表中的user_id存在NULL表示匿名订单-- 建表模拟 CREATE TABLE users (id INT PRIMARY KEY, customer_name VARCHAR(50)); INSERT INTO users VALUES (1, 张三), (2, 李四), (3, 王五); -- 查询下过单的用户注意orders 中可能包含 NULL 的 user_id SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);如果orders.user_id中有一个NULL这条查询不会返回任何行。原因在于 SQL 的三值逻辑id NOT IN (1, 2, NULL)等价于id 1 AND id 2 AND id NULL。id NULL的结果是UNKNOWN。整个AND链中有一个UNKNOWN最终结果就是UNKNOWN。WHERE只接受TRUE所以所有行都被过滤掉。解决办法有三种-- 方案一子查询中过滤 NULL推荐 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); -- 方案二使用 NOT EXISTS SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id); -- 方案三使用 LEFT JOIN IS NULL SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.id IS NULL;三种方案中NOT EXISTS通常最稳妥既能避免NULL问题又更容易利用索引。但这不意味着NOT IN一无是处只要你能保证子查询结果不含NULLNOT IN依然可读且高效。4.3 IN 子查询与 EXISTS 的选择IN和EXISTS在语义上很接近但它们的工作方式不同IN会先执行子查询生成结果集再对外层表的每一行做匹配。EXISTS对外层表的每一行去子查询中检查是否存在满足条件的行找到即停止。两种写法在数据量分布不同时性能差异很大。更关键的是EXISTS对NULL的容忍度更高因为它在“行存在性”层面判断不受NULL三值逻辑影响。一般建议子查询结果集较小、外层表较大IN通常不差。子查询结果集很大、外层表较小优先考虑EXISTS。对NULL敏感或无法确定子查询是否含NULL优先EXISTS。需要返回子查询中的多个列做进一步判断考虑JOIN。5. IN 与 JOIN 的取舍什么时候别用 IN如果说IN和OR的对比是语法层面那IN和JOIN的对比就是执行计划层面。很多开发者习惯性用IN做关联查询但有些场景改成JOIN更合适。5.1 数据量不同时的表现差异一个典型例子查“订单金额超过 100 的用户信息”。-- 用 IN思路先圈出符合条件的 user_id再匹配用户 SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE amount 100 ); -- 用 JOIN思路直接把用户和订单关联再过滤 SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id u.id WHERE o.amount 100;两个写法的语义相近但执行方式不同。如果orders表巨大而amount 100的订单也很多IN生成的中间结果集可能很大JOIN则可以通过驱动表的选择和索引优化得到更好的执行计划。但DISTINCT也是成本如果用户和订单是一对多关系需要去重。所以不能说JOIN永远更好要结合索引、数据分布和执行计划判断。5.2 什么时候必须用 JOIN如果你需要从子查询中获取多个字段IN就无能为力了。比如要查“最近下单超过 3 次的用户并且显示其最近一次下单时间”-- 错误IN 无法取出子查询中的其他字段 SELECT u.*, o.last_order_time FROM users u WHERE u.id IN (SELECT user_id, MAX(created_at) FROM orders ...); -- 语法错误 -- 正确用 JOIN 子查询 SELECT u.*, t.last_order_time FROM users u JOIN ( SELECT user_id, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id HAVING COUNT(*) 3 ) t ON t.user_id u.id;5.3 选择框架需求场景推荐工具说明判断某个值是否在集合中IN最直接判断存在性且外层表大EXISTS更容易走索引需要关联多表并返回多列JOIN表达能力更强排除某个集合先过滤NULL的NOT IN/NOT EXISTS注意NULL陷阱6. IN 与 NULL 的深入剖析NULL是 SQL 中最容易让人困惑的概念而IN与NULL的交互更是坑中坑。这一节从三值逻辑讲清楚。6.1 SQL 的三值逻辑SQL 中的逻辑判断不只是TRUE和FALSE还有第三个值UNKNOWN。NULL参与比较、算术、逻辑运算时结果往往是UNKNOWN。先看IN的基本判断规则表达式结果上海 IN (上海, 杭州)TRUE北京 IN (上海, 杭州)FALSENULL IN (上海, 杭州)UNKNOWNNULL IN (NULL)UNKNOWN上海 IN (上海, NULL)TRUE北京 IN (上海, NULL)UNKNOWN注意看最后一行这里没有返回FALSE而是UNKNOWN。原因是北京 NULL的结果是UNKNOWN而UNKNOWN OR FALSE的结果依然是UNKNOWN。6.2 WHERE 子句只接受 TRUEWHERE子句只保留结果为TRUE的行FALSE和UNKNOWN都会被过滤。所以SELECT * FROM orders WHERE city IN (NULL);返回空结果。这符合 SQL 标准但不符合很多人的直觉。6.3 NOT IN 的 NULL 陷阱再看NOT INSELECT * FROM users WHERE id NOT IN (1, 2, NULL);等价于SELECT * FROM users WHERE id 1 AND id 2 AND id NULL;id NULL的结果是UNKNOWN。SQL 中TRUE AND UNKNOWN的结果是UNKNOWN所以整行都不会被返回。这就是为什么NOT IN遇到NULL会返回空表的根本原因。6.4 如何安全地使用 NOT IN最佳实践是使用NOT IN之前必须确保子查询或列表中没有NULL。-- 安全写法一在子查询里过滤掉 NULL SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM orders WHERE user_id IS NOT NULL ); -- 安全写法二干脆不用 NOT IN SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );如果代码是给别人维护的或者你无法保证数据质量优先选NOT EXISTS。7. IN 的常见误用与安全问题7.1 列表过长导致数据库报错或性能下降不同数据库对IN列表长度有不同限制。Oracle 经典的限制是 1000 项超过会报ORA-01795: maximum number of expressions in a list is 1000。MySQL 虽然没这么严格的硬限制但几百上千项的IN列表会让优化器很吃力。解决方案把大列表拆分成多个小列表用OR拼接但注意长度和可读性。数据量大时改用临时表或JOIN。利用应用层分批查询。7.2 动态拼接 IN 列表的 SQL 注入风险这是安全领域的高危问题。很多开发者在业务代码里这样写// 危险示例千万不要这么写 String ids request.getParameter(ids); // 例如 1,2,3; DROP TABLE users String sql SELECT * FROM users WHERE id IN ( ids );如果ids来自用户输入攻击者可以构造恶意内容导致 SQL 注入。正确的做法是使用参数化查询// 以 JDBC 为例使用占位符 String sql SELECT * FROM users WHERE id IN (?, ?, ?); PreparedStatement ps connection.prepareStatement(sql); ps.setInt(1, 1); ps.setInt(2, 2); ps.setInt(3, 3);对于动态长度的列表可以使用ANY数组语法PostgreSQL或动态拼接占位符// 动态生成占位符但值通过参数绑定 ListInteger idList Arrays.asList(1, 2, 3); String placeholders idList.stream().map(id - ?).collect(Collectors.joining(, )); String sql SELECT * FROM users WHERE id IN ( placeholders ); PreparedStatement ps connection.prepareStatement(sql); for (int i 0; i idList.size(); i) { ps.setInt(i 1, idList.get(i)); }核心原则无论列表内容如何变化用户输入永远只能作为参数值进入 SQL不能作为 SQL 片段拼接。7.3 IN 列表中的隐式类型转换字符串列和整数列表混用可能导致索引失效或全表扫描-- 如果 id 是 VARCHAR 类型下面的写法可能让索引失效 SELECT * FROM users WHERE id IN (1, 2, 3);建议保持类型一致要么id是整数型要么列表里写字符串SELECT * FROM users WHERE id IN (1, 2, 3);7.4 值列表中有重复项IN值列表中有重复项不会影响查询结果因为集合语义天然去重。但这是一个代码气味说明生成列表的逻辑可能有问题建议在应用层去重。8. IN 的性能分析与优化建议8.1 不要凭感觉判断性能很多人一听到“用 JOIN 替代 IN”就觉得性能一定会提升。实际上IN在合理的数据量和索引条件下性能完全可以接受。问题的关键是你有没有看执行计划。以 MySQL 为例EXPLAIN SELECT * FROM orders WHERE city IN (上海, 杭州, 南京);观察type列如果出现ALL全表扫描就需要考虑加索引如果出现range或ref说明索引有效。8.2 IN 列表大小的影响IN列表比较小时优化器通常能转化为多个等值比较利用索引列表变大后可能退化为全表扫描或产生临时表。MySQL 中IN列表元素过多时优化器会将其与range条件等同最终表现取决于实际成本和统计信息。8.3 IN 子查询的优化方向子查询返回的结果集最好是有索引的唯一值或主键。避免在子查询中做不必要的全表扫描。必要时将子查询改为JOIN让优化器有更多选择。8.4 一条实用的判断路径遇到IN相关查询变慢按下面顺序排查用EXPLAIN查看执行计划是否走索引。看IN列表长度和数据量是否超出合理范围。检查子查询是否包含NULL、重复值或复杂计算。对比EXISTS和JOIN的执行计划选最优。如果列表是动态传入的确认没有造成隐式类型转换。9. 常见问题与排查方法下面用表格整理一下最常见的IN使用问题问题现象可能原因排查方式解决方案NOT IN查询返回空结果子查询或列表中存在NULL单独执行子查询检查是否有NULL子查询加IS NOT NULL或改用NOT EXISTSOracle 报 ORA-01795IN列表超过 1000 项统计列表长度拆分列表或用临时表JOIN查询走了全表扫描速度慢条件列没有索引或类型不匹配EXPLAIN查看执行计划加索引统一类型IN列表拼接导致 SQL 注入使用了字符串拼接 SQL代码审查检查持久层写法改用PreparedStatement参数化查询IN与AND混用时结果不对OR优先级高于预期检查 SQL 逻辑使用IN或加括号明确优先级大数据量IN子查询响应慢子查询结果集过大临时表开销高查看慢查询日志和执行计划改EXISTS或JOIN子查询内先过滤IN列表包含重复项应用层列表生成逻辑问题检查传入参数应用层去重10. 最佳实践生产环境中如何用好 IN10.1 优先使用参数化查询无论IN列表是固定值还是动态传入都推荐使用参数绑定避免 SQL 注入风险。这也符合数据库安全规范中的最小权限和输入校验原则。10.2 警惕 NOT IN 的 NULL 问题写NOT IN时默认假设数据里可能有NULL。建议在代码规范层面规定除非显式过滤了NULL否则不用NOT IN改用NOT EXISTS或LEFT JOIN。10.3 列表长度控制固定业务枚举通常没问题但保持列表可读性。动态数据集合建议控制在几十到几百个值以内超长时使用临时表或JOIN。子查询返回结果集用执行计划验证不要让子查询返回超大结果集。10.4 索引设计配合如果IN经常出现在查询条件中要为对应列设计索引。例如CREATE INDEX idx_orders_city ON orders(city);复合索引需要根据实际查询模式决定。比如经常用city status查询可以考虑CREATE INDEX idx_orders_city_status ON orders(city, status);10.5 日志与监控线上系统建议开启慢查询日志。凡是超过阈值比如 1 秒的 SQL无论是否包含IN都要记录下来并定期分析。很多IN问题不是一开始就存在而是随着数据量增长逐渐显现。10.6 测试环境先行修改涉及IN的查询逻辑前一定要在测试环境执行计划确认索引和行数符合预期。生产环境数据库的变更必须经过备份、灰度、回滚方案评估。11. 总结与后续学习方向IN是 SQL 中最常用、也最容易被低估的语法之一。它的核心价值在于用一种声明式的方式表达“值属于某个集合”的判断让查询更简洁、更接近业务语言。但同时IN与NULL、子查询、大数据量的交互非常微妙使用不当会产生“返回空结果”“全表扫描”“SQL 注入”等严重问题。读完这篇文章你应该能回答下面几个问题IN和OR有什么区别为什么推荐用INNOT IN返回空结果的根源是什么IN子查询和EXISTS、JOIN如何选择动态拼接IN列表如何避免 SQL 注入生产环境中IN查询变慢排查路径是什么下一步建议你打开本地数据库把文中的示例跑一遍尤其是NULL相关的场景。亲手看一眼“NOT IN遇到NULL返回空表”的现象比背十遍理论都有用。然后找一条业务里真实存在的慢 SQL用EXPLAIN分析它是否适合继续用IN。SQL 的学习很像搭积木单个语法用熟了后面学窗口函数、CTE、执行计划优化都会更顺。如果你对EXISTS、JOIN和IN的性能对比感兴趣可以继续深入了解数据库优化器的实现原理这是从“会写 SQL”走向“会调 SQL”的必经之路。