如何使用 EXISTS 子句的正确方法

在我的 SP 中，我正在创建一个临时表#PolicyNumbers并填充它Policy Numbers基于将提供给 SP 的参数。

CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
INSERT INTO #PolicyNumbers SELECT   PolicyNumber
                            FROM    PlazaInsuranceWPDataSet 
                            WHERE   State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,','))  
                            AND Coverage IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))
                            AND SICCode IN (SELECT * FROM [dbo].[StringOfStringsToTable](@SICCode,','))

下面是我正在使用的 SPEXISTS语句仅过滤那些PolicyNumber根据参数选择的。

WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber)

所以结果是#PolicyNumbers是否包含重复的保单编号：

然后在我的 SP 中我有SELECT语句和结果也包含重复PolicyNumbers:

SELECT 
            PolicyNumber,
            Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate,
            Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate,
            WrittenPremium
            FROM PlazaInsuranceWPDataSet

现在我正在使用EXIST陈述：

SELECT 
        PolicyNumber,
        Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate,
        Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate,
        WrittenPremium
        FROM PlazaInsuranceWPDataSet piwp
        WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber)

到目前为止一切都正确吗？ 如果是，那么为什么我使用的结果略有不同SELECT语句并传递相同的参数WHERE使用 IN (@MyParameter) 子句？

SELECT 
            PolicyNumber,
            Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate,
            Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate,
            WrittenPremium
            FROM PlazaInsuranceWPDataSet piwp
            WHERE    State IN (@State) 
                    AND SICCode IN (@SICCode)
                    AND Coverage IN (@Coverage)

两个查询之间的区别在于，第一个查询获取保单编号列表，其中表中至少有一行与State, SICode and Coverage。由于有多行PlazaInsuranceWPDataSet每个都可以有不同的State, SICode and Coverage（大概），这与仅选择行不同PlazaInsuranceWPDataSet匹配这三个谓词。

这是一个简化的示例：

Create Table PlazaInsuranceWPDataSet  (
    PolicyNumber varchar(1),
    State varchar(3))

Insert Into PlazaInsuranceWPDataSet 
Values  ('A', 'Qld'),
        ('A', 'NSW');

Create Table #PolicyNumbers (PolicyNumber char(1));
Insert Into #PolicyNumbers
Select PolicyNumber
  From PlazaInsuranceWPDataSet 
  Where State = 'Qld';

-- Returns all policy numbers where at least one row matches the predicate.
Select *
  From PlazaInsuranceWPDataSet As piwp
  Where Exists (Select 1 From #PolicyNumbers As pn
                Where pn.PolicyNumber = piwp.PolicyNumber);

-- Returns only rows of 'A' that match the filter predicate                    
Select *
  From PlazaInsuranceWPDataSet 
  Where State = 'Qld';

保单号“A”的一行（且只有一行）与条件匹配，因此当我们在临时表中记下该保单号并稍后进行比较时，我们将得到“A”的所有行。