将 IN 与 EXISTS 互换会产生不同的结果集

我正在尝试更新一个查询，该查询利用子句谓词中的IN运算符来比较潜在的性能改进，并更好地了解当两者互换时幕后发生的事情。据我了解，在实践中，查询优化器会尽可能以相同的方式对待and 。WHEREEXISTSEXISTSIN

我注意到当使用IN运算符运行查询时，它会返回所需的结果集。但是，当我用等效项替换它时EXISTS，它会从我要过滤的主表中提取所有值。它忽略传递给的提供的输入值EXISTS (SELECT ...并返回所有可能的不同值。

用例相对简单：查询接受一个竖线分隔的@Series字符串，该字符串最多可以包含 4 个值，例如S1|S2|S3|S4or S2|S4。从这里我string_split输入一个表变量@SeriesSplit来确定对应的内部[SeriesId]为[Series]. 然后过滤返回的结果集以排除[Series]未通过的结果集。

为了说明，这里有一个类似的表定义：

DROP TABLE IF EXISTS [dbo].[Document]
IF OBJECT_ID('[dbo].[Document]', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Document] (
        [DocumentId] bigint IDENTITY(1,1) NOT NULL
        ,[DocumentSeriesId] [tinyint] NOT NULL
        ,CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED ([DocumentId] ASC)
        ,INDEX [IX_Document_SeriesId] NONCLUSTERED ([DocumentSeriesId] ASC)
    );
END;
GO

用虚拟数据填充测试表。

SET IDENTITY_INSERT [dbo].[Document] ON;
;WITH [DocumentSeed] AS (
    SELECT
        1 AS [DocumentId]
    UNION ALL
    SELECT
        [DocumentId] + 1
    FROM
        [DocumentSeed]
    WHERE
        [DocumentId] < 2048)

INSERT INTO [dbo].[Document] ([DocumentId], [DocumentSeriesId])
SELECT
    [DocumentId]
    ,ABS(CHECKSUM(NEWID()) % 4) + 1
FROM
    [DocumentSeed] OPTION (MAXRECURSION 2048);
SET IDENTITY_INSERT [dbo].[Document] OFF;

首先，使用IN运算符并返回所需结果的查询。

-- Specify the Document Series to be returned.
DECLARE @Series varchar(12) = 'S1|S2'
-- Split the user input and insert it into a table variable.
DECLARE @SeriesSplit table ([SeriesId] tinyint, [Series] varchar(2))
BEGIN
INSERT INTO @SeriesSplit ([SeriesId], [Series])
SELECT
    CASE [value] WHEN 'S1' THEN 1 WHEN 'S2' THEN 2 WHEN 'S3' THEN 3 WHEN 'S4' THEN 4 ELSE 5 END AS [SeriesId]
    ,LTRIM(RTRIM([value])) AS [Series]
FROM
    string_split(@Series,'|')
WHERE
    [value] <> ''
END;

-- Return the result set of desired [DocumentSeriesId]
-- In the real use case, DISTINCT is not used and more columns are returned.
-- However, to illustrate the issue at hand, return only the [DocumentSeriesId] as this is what we are filtering off.
SELECT DISTINCT
    D1.[DocumentSeriesId]
FROM
    [dbo].[Document] D1
WHERE
    D1.[DocumentSeriesId] IN (SELECT SS.[SeriesId] FROM @SeriesSplit SS)

根据需要输出两行。执行计划 (PasteThePlan)显示它执行 aDistinct Sort以过滤适当的行。

如果我将WHERE子句更改为 utilize EXISTS，我会看到所有四种可能的结果，即使我已设置只返回两种结果。

-- Specify the Document Series to be returned.
DECLARE @Series varchar(14) = 'S1|S2'
-- Split the user input and insert it into a table variable.
DECLARE @SeriesSplit table ([SeriesId] tinyint, [Series] varchar(4))
BEGIN
INSERT INTO @SeriesSplit ([SeriesId], [Series])
SELECT
    CASE [value] WHEN 'S1' THEN 1 WHEN 'S2' THEN 2 WHEN 'S3' THEN 3 WHEN 'S4' THEN 4 ELSE 5 END AS [SeriesId]
    ,LTRIM(RTRIM([value])) AS [Series]
FROM
    string_split(@Series,'|')
WHERE
    [value] <> ''
END;

-- Return the result set of desired [DocumentSeriesId]
-- In the real use case, DISTINCT is not used and more columns are returned.
-- However, to illustrate the issue at hand, return only the [DocumentSeriesId] as this is what we are filtering off.
SELECT DISTINCT
    D1.[DocumentSeriesId]
FROM
    [dbo].[Document] D1
WHERE
    EXISTS (SELECT SS.[SeriesId] FROM @SeriesSplit SS JOIN [dbo].[Document] D2 ON SS.[SeriesId] = D2.[DocumentSeriesId])

执行计划 (PasteThePlan)因这个小改动而大不相同。它正在抛出谓词中存在的警告No Join Predicate。Left Semi Join我的假设是WHERE EXISTS ...隐含地满足这个论点，就像它对WHERE [DocumentSeriesId] IN .... 但是，假设Left Semi Join运算符在第二个输入中存在匹配行时返回初始输入中的每一行，并且由于No Join Predicate警告存在，查询优化器假定每一行都是匹配行。因此，所有后续批处理操作都针对表中的所有 2048 行运行。

我可以研究什么来更好地解释执行计划所描述的内容，以便我可以了解如何正确解决问题？

或者，在EXISTS基于静态输入过滤结果集时，我是否只是忽略了运算符的目的？