SQL中优化IN的几种做法

作者：袖梨 2026-08-03

SQL中优化IN的几种做法并不只看表面做法，关键还要理解相关条件、限制和后续影响。

一、前言

最近对一个存储过程进行优化，这里记录一下IN在SQLServer中的一些优化和情况。

二、能代替IN的几种方法效率对比

所以 UNION ALL其实是效率最好的，也就是 =ANY()

三、实际写法

3.1 IN

Select * from TableA where name IN ('A','B','C');

3.2 OR

Select * from TableA where name='A' OR name='B' OR name='C';

3.3 EXISTS

SELECT *FROM TableA aWHERE EXISTS (SELECT 1FROM ( VALUES  ('A'),('B'),('C')) AS tmp(name)

3.4 JOIN

-- SQL Server/PostgreSQL/MySQL 8.0+ 语法SELECT TableA.*FROM TableAJOIN ( VALUES  ('A'),('B'),('C')) AS tmp(name)ON TableA.name = tmp.name

3.4 UNION ALL

Select * from TableA  where name='A'   union allSelect * from TableA  where name='B'   union allSelect * from TableA  where name='C '  或Select * from TableA where name = ANY('A','B','C');

三、存储过程实例

实现

 SELECT  * from TableA WHERE   A.name IN  ('A','B','C')    AND A.types IN ('1','3','7','8')    AND A.age IN ('33','35','39')

存储过程

declare  @name  varchar(100)='A,B,C',@type  varchar(100)='1,3,7,8',@age  varchar(100)='33,35,39'--列转行 if object_id('tempdb..#MyTemp') is not null     Begin      DROP TABLE #MyTemp  End    Select * Into #MyTempTable1 From      (      SELECT B.id,B.typeid        FROM   (           SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@name,',', '</v><v>') + '</v>')          ) A          OUTER APPLY(         SELECT id = N.v.value('.', 'nvarchar(100)'),typeid=1       FROM   A.[value].nodes('/v') N(v)        ) B        Union      SELECT D.id,D.typeid        FROM   (           SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@type,',', '</v><v>') + '</v>')          ) C          OUTER APPLY(         SELECT id = N.v.value('.', 'nvarchar(100)'),typeid=2       FROM   C.[value].nodes('/v') N(v)        ) D    Union      SELECT D.id,D.typeid        FROM   (           SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@age,',', '</v><v>') + '</v>')          ) E          OUTER APPLY(         SELECT id = N.v.value('.', 'nvarchar(100)'),typeid=3       FROM   C.[value].nodes('/v') N(v)        )F ) as T   --EXISTS 子查询 Select * from TableA A where    A.name IN  (select id from #MyTemp where typeid=1)    AND A.types IN (select id from #MyTemp where typeid=2)    AND A.age IN (select id from #MyTemp where typeid=3)

四、实际情况

情况1

有一个很奇怪的情况，当@name='A’时，存储过程中

Select * from TableA A where    A.name IN  (select id from #MyTemp where typeid=1)    AND A.types IN (select id from #MyTemp where typeid=2)    AND A.age IN (select id from #MyTemp where typeid=3)

A.name... 比 A.name='A' 或 A.name in ('A') 查询时间多了一倍。 types、age 确没影响

改成Join后，得到解决：

Select * from TableA A   JOIN #MyTempTable1 B   ON B.id = A.DATAAREAID  AND B.typeid=1WHERE   A.types IN (select id from #MyTemp where typeid=2)    AND A.age IN (select id from #MyTemp where typeid=3)

情况2

在in 不走索引的情况下，union all 效率最高

再改进：

Select * from TableA A   JOIN #MyTempTable1 B   ON B.id = A.DATAAREAID  AND B.typeid=1WHERE   A.types = ANY(select id from #MyTemp where typeid=2)    AND A.age = ANY(select id from #MyTemp where typeid=3)