SQL IN 用法详解：从基础语法到慢SQL优化与安全实践

SQL 里的 IN很多人每天都在写但写错的人也不少。这次我们聚焦数据库管理系统中最常用的过滤条件之一IN。别小看这个关键字它既能简化查询、配合子查询完成多表过滤也可能因为NULL、NOT IN、数据集过大变成慢 SQL 的源头处理不好还会给 SQL 注入留入口子。这篇文章会把IN的语法、执行逻辑、常见陷阱、性能对比、批量任务用法和安全性一起讲清楚适合正在学 SQL 的初学者也适合想优化慢 SQL、排查接口查询问题的开发同学。先说结论IN不是一个“简单到不用学”的语法点它背后隐藏着结果集比较、NULL 语义、子查询优化、索引命中、批量操作和注入风险这几个层面的问题。文章会给出可直接运行的示例、验证方法和排查思路方便你在 MySQL、PostgreSQL、SQL Server 等常见数据库管理系统中做对照测试。1. 核心能力速览能力项说明操作符类型SQL 条件过滤操作符用于判断字段值是否落在指定集合内常用场景等值集合匹配、子查询过滤、批量更新/删除、接口查询参数拼接替代方案OR 多条件、EXISTS 子查询、JOIN 关联、临时表关联常见风险NULL 值导致NOT IN结果为空、大数据集合命中索引失败、动态拼接引发 SQL 注入兼容性所有主流关系型数据库均支持包括 MySQL、PostgreSQL、Oracle、SQL Server性能关注点集合大小、子查询结果量、字段索引、数据库优化器行为适合读者SQL 初学者、后端开发、数据分析师、运维工程师2. IN 操作符是什么IN用于判断某个字段的值是否包含在一个给定的值列表或子查询结果集中。逻辑上等价于多个OR条件的简写。SELECT * FROM employees WHERE department_id IN (10, 20, 30);这条语句会返回department_id为 10、20、30 的员工记录。如果不使用IN写成OR是这样SELECT * FROM employees WHERE department_id 10 OR department_id 20 OR department_id 30;两种写法结果一致但IN的语义更清晰也更容易维护。当列表项很多时OR写法会变得冗长而IN只需要维护一个集合。2.1 IN 与其他过滤条件的区别IN属于集合成员判断它和比较运算符、范围判断BETWEEN ... AND ...、模糊匹配LIKE定位在不同维度单值等值比较。BETWEEN ... AND ...范围比较包含边界值。LIKE字符串模式匹配支持通配符。IN离散集合成员判断集合可以是显式值列表也可以是子查询结果。如果待匹配的值是连续的比如“工资在 5000 到 10000 之间”优先用BETWEEN如果是若干个不连续的值比如“部门编号是 10、20、30”用IN更合适如果匹配条件有前缀或模糊含义比如“名字以 张 开头”只能选LIKE。3. 基础语法与结果集判断IN的语法结构非常简单但使用时必须明确结果集判断规则。3.1 标准语法SELECT 列名 FROM 表名 WHERE 列名 IN (值1, 值2, 值3 ...);值的类型需要与列的类型兼容。例如字符串列要用引号包围SELECT * FROM customers WHERE country IN (CN, US, JP);日期列按照数据库系统的日期格式书写SELECT * FROM orders WHERE order_date IN (2024-01-01, 2024-06-01, 2024-12-01);3.2 判断规则IN的语义是“字段值等于列表中任意一个值”判断结果有三种情况匹配成功字段值等于列表中某个值该行进入结果集。匹配失败字段值不等于列表中任何一个值该行被过滤。NULL 值参与字段值为 NULL 时IN判断不会返回 TRUE也不会返回 FALSE而是返回 UNKNOWN行被过滤掉。这里有一个实际开发中容易忽略的细节WHERE column IN (1, 2, 3)不会返回column IS NULL的行即使 NULL 不在列表里也不能通过“不等于列表”来筛选出 NULL 行。需要筛选 NULL 时必须显式使用IS NULL。3.3 与 DISTINCT 搭配注意输出重复行IN本身不负责去重。如果子查询结果集中存在重复值IN不会报错也不影响查询结果因为判断重复值没有额外代价。但如果查询目标需要去重应该使用DISTINCT或者在子查询中直接去重。SELECT DISTINCT department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);这个写法同时演示了“清洗 SQL 语句去重”场景先通过IN圈定部门范围再用DISTINCT对输出结果去重。4. 子查询中使用 ININ的值列表不仅可以手动写出也可以来自子查询。这是IN非常核心的用法常用于两表关联过滤。4.1 基本子查询示例SELECT employee_id, employee_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location Shanghai );执行逻辑是先执行子查询拿到所有位于上海部门的department_id列表再对employees表逐行判断department_id是否在该列表中。需要注意的是子查询结果集会作为内存中的临时集合参与判断。当子查询返回的数据量很大时会带来两个问题内存占用上升。大集合的成员判断可能导致索引使用不理想。4.2 子查询结果列为 NULL 的影响如果子查询结果集中包含 NULL 值例如SELECT * FROM employees WHERE department_id NOT IN ( SELECT department_id FROM departments );当departments.department_id中存在 NULL 时整条NOT IN查询的结果会变得不可信。原因在NOT IN的 NULL 语义中下面会单独展开。4.3 子查询中使用聚合函数IN也常和聚合结果配合使用。例如查询工资高于平均工资的员工SELECT employee_id, employee_name, salary FROM employees WHERE salary IN ( SELECT MAX(salary) FROM employees );但这里要注意语义不同IN (SELECT MAX(salary) ...)只匹配工资恰好等于最高工资的行如果需求是“高于平均工资”需要改用或SELECT employee_id, employee_name, salary FROM employees WHERE salary ( SELECT AVG(salary) FROM employees );IN判断的是“等于集合中的某个值”集合判断不是区间比较这一点在写 SQL 时很容易弄混。窗口函数、聚合函数和IN配合使用时要时刻想清楚集合与比较的关系。5. IN 与 EXISTS 深度对比IN和EXISTS都能实现子查询过滤但执行逻辑不同性能和使用边界也不同。5.1 执行逻辑差异IN的执行逻辑是子查询先生成结果集外部查询再判断字段值是否在结果集中。EXISTS的执行逻辑是对外部查询的每一行执行一次子查询只要子查询返回至少一行条件就成立。SELECT employee_id, employee_name FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE d.department_id e.department_id AND d.location Shanghai );5.2 什么时候用 EXISTS当子查询的数据量大且外部表的数据量不大时EXISTS通常更高效因为它不需要构建并维护一个完整的结果集。典型场景子查询部分存在大量数据。外部查询表行数有限。子查询与外部查询有相关条件。SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.order_date 2024-01-01 );这个查询会返回 2024 年以来下过单的客户。相关子查询的条件o.customer_id c.customer_id决定了每一行都要到订单表里确认一次。5.3 什么时候用 IN当子查询结果集小而且外部表数据量大时IN的性能通常更稳定。数据库优化器会先把子查询结果集物化成一个临时集合然后对外部表做遍历判断。典型场景子查询返回的是少量 ID 列表。外部查询需要过滤的字段有索引。子查询不依赖外部查询属于无关联子查询。SELECT * FROM employees WHERE department_id IN (10, 20, 30);这种写法每次执行都是固定的值集合数据库优化器可以走索引扫描。5.4 一个简单的选择原则不要把IN和EXISTS当成完全等价的替代品它们的结果集在大多数情况下一致但在 NULL 语义上存在差异。实操中更稳妥的判断标准是子查询无关联结果集小优先IN。子查询有关联外部表小优先EXISTS。数据量不确定先看执行计划再决定。6. NOT IN 与 NULL 的经典陷阱NOT IN是开发中踩坑最多的地方。问题的根源是 SQL 的三值逻辑TRUE、FALSE、UNKNOWN。6.1 三值逻辑回顾在 SQL 中NULL 不是值而是“未知”。任何与 NULL 的比较结果都不是 TRUE 或 FALSE而是 UNKNOWN。只有IS NULL、IS NOT NULL能直接判断 NULL 本身。6.2 问题复现看一个常见的例子假设departments表数据如下department_iddepartment_name10研发部20市场部NULL临时部门执行SELECT * FROM employees WHERE department_id NOT IN ( SELECT department_id FROM departments );你会惊讶地发现结果集是空的即使确实存在部门不在这个查询结果中的员工。原因在于NOT IN等价于“字段值 集合中的每一个值”。一旦集合中存在 NULL那么“字段值 NULL”这一项的结果是 UNKNOWN而 UNKNOWN 参与 AND 逻辑时整体结果不可能为 TRUE。于是所有行都被过滤掉了。6.3 验证方式验证时可以直接在数据库管理系统里执行SELECT 1 WHERE 10 NOT IN (10, 20, NULL); SELECT 1 WHERE 10 NOT IN (20, NULL);第一条返回空第二条也返回空。因为10 NULL是 UNKNOWN导致整体条件无法成立。6.4 解决办法最直接的办法是过滤掉子查询结果中的 NULLSELECT * FROM employees WHERE department_id NOT IN ( SELECT department_id FROM departments WHERE department_id IS NOT NULL );或者用NOT EXISTS替代SELECT * FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM departments d WHERE d.department_id e.department_id );第二种写法在语义上更安全结果完全符合直觉也避免了大集合中 NULL 导致的问题。7. IN 与 JOIN 的选择IN和JOIN都可以实现“一个表中取另一个表中存在匹配”的逻辑但结果集有区别性能表现也不同。7.1 输出结果差异JOIN会根据连接条件的匹配关系返回组合行。如果右表存在重复匹配左表的行会被复制多次。SELECT e.employee_id, e.employee_name FROM employees e JOIN departments d ON d.department_id e.department_id WHERE d.location Shanghai;如果复用同一个 script 没有做去重这个查询可能返回重复的employee_id。而IN的语义是过滤不会因为右表匹配多行而重复输出左表行SELECT employee_id, employee_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location Shanghai );7.2 性能比较如果只需要判断存在性IN和EXISTS通常比JOIN更合适因为JOIN需要维护连接结果数据量大时会增加排序和临时表开销。如果需要同时输出右表中的字段比如部门名称那就必须使用JOINSELECT e.employee_id, e.employee_name, d.department_name FROM employees e JOIN departments d ON d.department_id e.department_id WHERE d.location Shanghai;7.3 结论只判断是否存在优先考虑IN、EXISTS。需要右表字段使用JOIN。大批量、存在 NULL用EXISTS或NOT EXISTS。需要保留左表无匹配的行使用LEFT JOIN后再过滤IS NULL而不是用NOT IN。8. 批量操作中的 ININ不只是 SELECT 查询的专属。UPDATE 和 DELETE 也经常使用IN圈定数据范围。8.1 批量更新UPDATE employees SET status inactive WHERE department_id IN (10, 20, 30);这个操作会把三个部门的员工状态批量修改。生产环境下执行大范围更新前务必先使用相同条件的 SELECT 确认影响行数。8.2 批量删除DELETE FROM order_details WHERE order_id IN ( SELECT order_id FROM orders WHERE order_date 2020-01-01 );这条语句用于删除指定日期之前订单的明细数据。批量删除时要特别注意子查询返回的 NULL 值如果order_id可能为 NULL建议先过滤DELETE FROM order_details WHERE order_id IN ( SELECT order_id FROM orders WHERE order_date 2020-01-01 AND order_id IS NOT NULL );8.3 大批量参数集合的处理接口开发中经常出现IN列表非常大的情况比如前端传入了数千个 ID。直接把几千个 ID 拼进 SQL 会有几个问题SQL 语句过长数据库解析开销上升。索引命中可能失效。日志系统会记录超长 SQL影响排查。动态拼接字符串存在 SQL 注入风险。更稳妥的做法是使用临时表或数组参数。以 PostgreSQL 为例SELECT * FROM employees WHERE department_id ANY(ARRAY[10, 20, 30]);在 MySQL 中可以使用 JSON 参数配合JSON_TABLE或在应用层分批查询。分批查询时要注意控制每批的大小比如每批 500 到 1000 个 ID然后合并结果。9. IN 与 SQL 注入风险SQL 注入是所有数据库操作绕不开的安全问题。热搜词里也出现了“SQL 注入万能密码绕过”“pikachu 靶场通关 SQL 注入”等关键词说明这是很多人实际遇到的问题。IN作为高频操作符也经常成为注入点尤其是使用字符串拼接时。9.1 拼接场景的风险一个典型的错误写法ids request.args.get(ids) sql SELECT * FROM users WHERE id IN ( ids ) cursor.execute(sql)如果ids是1, 2, 3正常执行如果攻击者传入1) OR 11 --整个 SQL 语义就变了可能返回全部用户数据。9.2 安全写法使用参数化查询。Python MySQL 示例import pymysql connection pymysql.connect( host127.0.0.1, userroot, passwordpassword, databasetest_db ) ids [10, 20, 30] placeholders ,.join([%s] * len(ids)) sql fSELECT * FROM employees WHERE department_id IN ({placeholders}) with connection.cursor() as cursor: cursor.execute(sql, ids) result cursor.fetchall() print(result)核心原则是永远不要直接拼接外部输入到 SQL 语句中。参数化查询让数据库把传入数据当值处理而不是当 SQL 代码处理从根源上阻断注入。9.3 其他防护对接口入参做白名单校验比如只允许数字和逗号。控制单次查询的 ID 数量上限。数据库账号遵循最小权限原则业务账号不给 DDL 权限。定期查看数据库慢查询日志和错误日志发现异常 SQL 及时处理。10. 性能观察与慢 SQL 排查从热搜词可以看到大量与“慢 SQL 优化”、“SQL 语句去重”、“SQL 优化”相关的内容说明性能问题是开发者最关心的方向之一。IN导致的慢 SQL 通常有几种典型原因。10.1 子查询结果集过大IN子查询返回结果集非常大时优化器可能不会使用索引而是选择全表扫描或临时表哈希匹配。排查方法查看执行计划。检查子查询返回行数。避免在IN子查询中使用无必要的全表扫描。10.2 字段无索引IN判断的字段如果缺少索引外部表的每一行都要做一次集合遍历。给高频查询的字段添加索引是常见优化手段。CREATE INDEX idx_employee_department_id ON employees(department_id);索引创建后IN查询可能从全表扫描变成索引范围扫描性能差距往往非常明显。但不要盲目建索引索引会占用磁盘空间并降低写入速度需要在写入性能和查询性能之间做平衡。10.3 大量 OR 条件有些查询原本可以用IN却被写成了大量ORWHERE col 1 OR col 2 OR col 3 OR col 4 OR col 5改写成IN后语义不变SQL 文本长度更短优化器处理也更方便WHERE col IN (1, 2, 3, 4, 5)10.4 慢 SQL 定位方法MySQL 开启慢查询日志。PostgreSQL 使用pg_stat_statements。SQL Server 使用动态管理视图。Oracle 使用 AWR 报告。定位到慢 SQL 后先用EXPLAIN或EXPLAIN ANALYZE查看执行计划重点观察type 是否为 ALL全表扫描。rows 预估是否过大。Extra 中是否出现 Using temporary、Using filesort。11. 不同数据库系统中的写法差异IN的语义在主流数据库中基本相同但语法细节和优化器行为存在差异。数据库基本 IN 用法数组/集合写法注意点MySQLWHERE id IN (1, 2, 3)不支持数组直接传入子查询结果集过大时性能波动明显PostgreSQLWHERE id IN (1, 2, 3)WHERE id ANY(ARRAY[1,2,3])支持数组NULL 语义需注意SQL ServerWHERE id IN (1, 2, 3)表值参数NOT IN遇到 NULL 时结果为空OracleWHERE id IN (1, 2, 3)WHERE id IN (SELECT column_value FROM TABLE(...))列表项超过 1000 会报错需要分段或临时表以 Oracle 为例IN列表超过 1000 项会抛出 ORA-01795 错误解决思路是把列表拆分或者把数据插入临时表后再关联SELECT * FROM employees WHERE department_id IN ( SELECT department_id FROM temp_dept_ids );12. 常见问题与排查方法问题现象可能原因排查方式解决方案NOT IN查询结果为空子查询结果集中存在 NULL单独执行子查询检查是否有 NULL过滤 NULL 或改用NOT EXISTSIN查询速度慢字段无索引或子查询结果集过大查看执行计划确认是否全表扫描添加索引或改为EXISTS/JOININ列表太长SQL 报错数据库对列表项数量有限制查看数据库错误日志分段查询、临时表或表值参数使用IN查询后出现重复行JOIN导致结果重复而非IN本身检查 SQL 中是否同时存在 JOIN 和 IN明确需求使用DISTINCT或修改过滤逻辑动态拼接IN列表被注入外部输入直接拼接到 SQL 字符串检查应用日志中的原始 SQL 语句使用参数化查询、白名单校验写IN时包含 NULL 值语义理解错误认为 NULL 会匹配单独测试WHERE col IN (1, NULL)显式使用IS NULL或IS NOT NULL子查询返回大量数据内存占用高子查询结果集被完整物化使用EXISTS替代或缩小结果集改成关联子查询或增加过滤条件13. 最佳实践与使用建议日常开发中把IN用对不是难事但需要用一套稳定的原则来约束。13.1 查询编写原则值列表固定且数量少时直接用IN值列表。子查询结果集可能包含 NULL 时使用NOT IN前先过滤。子查询有关联条件时优先考虑EXISTS。需要输出右表字段时使用JOIN。列表项很多时改用临时表、数组参数或分批查询。13.2 代码审查清单检查 SQL 是否存在NOT IN子查询且未处理 NULL。检查外部输入是否拼接进IN列表。检查IN列表数量是否超过数据库限制。检查是否需要DISTINCT去重。检查子查询结果集大小和执行计划。13.3 测试环境验证方法如果使用的是 MySQL可以开启慢查询日志并设置阈值SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;之后执行包含IN的查询语句观察哪些 SQL 超过耗时阈值再用EXPLAIN分析执行计划。如果使用的是 PostgreSQL可以用EXPLAIN ANALYZE直接观察实际执行时间EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id IN (10, 20, 30);注意EXPLAIN ANALYZE会实际执行 SQL不要在超大表上盲目使用避免造成不必要的数据库负载。13.4 接口服务中的批量查询建议后端开发中IN最常见的场景是根据前端传入的 ID 列表查询数据。推荐的做法是限制最大 ID 数量。使用参数化查询。增加 Redis 缓存热点数据。批量查询失败时设置重试但要控制重试次数避免对数据库造成压力。14. 总结与下一步这次我们把 SQL 中的IN完整拆了一遍基本语法、子查询、IN与EXISTS和JOIN的取舍、NOT IN的 NULL 陷阱、批量操作、SQL 注入风险和慢 SQL 排查方法。从实际开发角度来说最值得先验证的是NOT IN遇到 NULL 的行为因为这个问题隐蔽且破坏力大最容易在联调测试中漏过去。下一步建议你在自己的数据库管理系统里做一组对照实验建一张员工表和一张部门表插入少量测试数据其中部门表包含一条department_id IS NULL的记录。执行NOT IN查询确认返回结果为空。删除 NULL 记录后再次执行观察结果变化。使用NOT EXISTS改写确认结果符合预期。在一个小表上执行EXPLAIN观察IN子查询的访问类型和预估行数。把这一套步骤跑完你对IN的理解就不再停留在“能查出来”的层面而是能判断什么时候该用IN、什么时候该换EXISTS、什么时候必须警惕 NULL 语义这份经验在慢 SQL 优化和接口查询开发中都会用到。