T-SQL中高效筛选大量元素（超100个）的方法

当需要筛选的元素数量超过100个时，IN子句会逐渐出现性能瓶颈（比如查询计划优化困难、语句过长难以维护），以下是几个更高效的实用方案：

1. 使用临时表或表变量 #

把需要筛选的元素存入临时表（或表变量），通过JOIN或EXISTS关联原表查询，这种方式能让SQL Server优化器生成更高效的执行计划，尤其适合超大量筛选值的场景。

示例代码： #

-- 创建带主键的临时表，提升关联查询速度
CREATE TABLE #FilterProducts (Product VARCHAR(50) PRIMARY KEY);
INSERT INTO #FilterProducts (Product)
VALUES ('a'), ('b'), ('c'), ...; -- 插入所有需要筛选的元素

-- 关联原表查询
SELECT t1.*
FROM t1
JOIN #FilterProducts fp ON t1.Product = fp.Product;

-- 使用完清理临时表
DROP TABLE #FilterProducts;

如果是几百个量级的筛选值，也可以用表变量简化操作：

DECLARE @FilterProducts TABLE (Product VARCHAR(50) PRIMARY KEY);
INSERT INTO @FilterProducts (Product) VALUES ('a'), ('b'), ...;

SELECT t1.*
FROM t1
WHERE EXISTS (SELECT 1 FROM @FilterProducts fp WHERE fp.Product = t1.Product);

2. 使用表值参数（TVP） #

如果是从应用程序调用T-SQL，表值参数是更优雅的选择——它允许直接将应用中的集合（比如List、DataTable）传递到SQL中，无需拼接冗长的IN子句，同时性能优异。

示例代码： #

首先定义用户自定义表类型：

CREATE TYPE ProductListType AS TABLE (Product VARCHAR(50));

然后在查询或存储过程中使用：

DECLARE @Products ProductListType;
INSERT INTO @Products VALUES ('a'), ('b'), ...; -- 也可直接从应用程序传入参数

SELECT t1.*
FROM t1
JOIN @Products p ON t1.Product = p.Product;

3. 用CTE构造筛选列表（适合有规律的筛选值） #

如果筛选值是有规律的（比如连续ID、按固定规则生成的字符串），可以用CTE生成筛选列表，再关联查询，避免手动插入大量值。

示例代码： #

WITH FilterProducts AS (
    SELECT 'prod_001' AS Product
    UNION ALL
    SELECT 'prod_002' UNION ALL
    SELECT 'prod_003' UNION ALL
    ... -- 或用递归CTE生成连续序列
)
SELECT t1.*
FROM t1
JOIN FilterProducts fp ON t1.Product = fp.Product;

内容的提问来源于stack exchange，提问作者Jan Jankowski Janek