基于可变JSON数组过滤SQL表行的最优方案及替代方法问询

有一张包含大量行和id列的表A，同时有一个可变变量@Json，它的取值可能是以下三种情况之一：

• null
• 空JSON数组'[]'
• 非空JSON数组（例如'[1,2,3]'）

需要根据@Json的值过滤表A的行，规则如下：

1. 当@Json为null时，返回表A的所有行
2. 当@Json为空数组时，返回空结果集
3. 当@Json为非空数组时，返回表A中id存在于数组内的行

限制条件：不能使用临时表和CTE，必须通过子查询实现过滤

目前已经采用的解法如下：

DECLARE @Json NVARCHAR(max) = null

SELECT  *
FROM A a
LEFT JOIN OPENJSON(@Json) WITH(id INT '$') J ON a.id = J.id
WHERE @Json is null or J.Id is not null

现有方案的优缺点分析 #

这个方案逻辑上符合需求，但算不上最优，具体分析：

• null场景：当@Json为null时，LEFT JOIN OPENJSON(@Json)会返回空结果集，但WHERE条件@Json is null会保留所有行。虽然SQL Server查询优化器可能做优化，但针对大表来说，不必要的JOIN操作仍可能带来额外开销。
• 空数组场景：OPENJSON返回空结果集，LEFT JOIN后J.Id全为null，WHERE条件不满足，结果集为空，这部分逻辑正确。
• 非空数组场景：LEFT JOIN能匹配到id在数组中的行，逻辑正确，但相比半连接（如EXISTS），在数组元素较多时性能可能稍差，还可能出现重复行（若表A存在重复id）。

符合要求的替代方法 #

方法1：使用EXISTS子查询结合OPENJSON #

DECLARE @Json NVARCHAR(max) = null

SELECT *
FROM A a
WHERE @Json IS NULL
   OR EXISTS (
       SELECT 1
       FROM OPENJSON(@Json) WITH(id INT '$') J
       WHERE J.id = a.id
   )

该方法的优势：

• null场景下直接跳过子查询，返回全表，避免不必要的JOIN开销，性能更优。
• 空数组场景下，EXISTS子查询返回false，结果集为空，符合规则。
• 非空数组场景下，半连接逻辑避免重复行，查询优化器对EXISTS的支持通常更好，性能更稳定。

方法2：动态SQL拼接（需注意SQL注入） #

若允许使用动态SQL，可根据@Json生成最精简的查询语句：

DECLARE @Json NVARCHAR(max) = null
DECLARE @Sql NVARCHAR(max)

SET @Sql = 'SELECT * FROM A a WHERE 1=1'

IF @Json IS NOT NULL
BEGIN
    SET @Sql = @Sql + ' AND a.id IN (SELECT id FROM OPENJSON(@Json) WITH(id INT ''$''))'
END

EXEC sp_executesql @Sql, N'@Json NVARCHAR(max)', @Json

说明：

• 空数组时，IN子查询返回空，结果集自动为空，符合规则。
• 该方法能针对不同场景生成最简洁的查询，性能最优，但必须确保@Json来源安全，防止SQL注入风险。

内容的提问来源于stack exchange，提问作者Alexander