如何使用EXISTS子句的正确方法 [英] How would be the right way to use EXISTS clause

问题描述

在我的SP中，我将创建临时表#PolicyNumbers，并根据将提供给SP的参数用Policy Numbers填充它.

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,','))

在我的SP的下面，我正在使用EXISTS语句仅过滤那些 根据参数选择的PolicyNumber.

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

所以#PolicyNumbers的结果确实包含重复的PolicyNumbers:

然后我的SP中有SELECT语句，结果还包含重复的PolicyNumber s:

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语句并在使用(IN)(@MyParameter)在WHERE子句中传递相同的参数时却会有稍微不同的结果?

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和Coverage.由于PlazaInsuranceWPDataSet中有多行，并且每行可以具有不同的State，SICode和Coverage(大概)，因此这与从PlazaInsuranceWPDataSet仅选择与这三个谓词匹配的行不同. /p>

这是一个简化的示例:

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"的所有行

In my SP I am creating a temp table #PolicyNumbers and populating it with Policy Numbers based on the parameters that will be supplied to the 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,','))

Further below in my SP I'm using EXISTS statement to filter only those PolicyNumbers that been selected based on parameters.

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

So the result from #PolicyNumbers DOES contain duplicates PolicyNumbers:

Then furthure in my SP I have SELECT statement and the result also contains duplicate PolicyNumbers:

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

And now I am using EXIST statement:

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)

Is everything correct so far? If yes, then why do I have slightly different result using SELECT statement and passing same parameters in WHERE clause using 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)

解决方案

The difference between your two queries, is that the first gets a list of policy numbers where at least one of the rows in the table matches on State, SICode and Coverage. Since there are multiple rows in PlazaInsuranceWPDataSet and each can have different State, SICode and Coverage (presumably), this is not the same as selecting only rows from PlazaInsuranceWPDataSet that match on those three predicates.

Here is a simplified example:

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';

One (and only one) of the rows of policy number 'A' matches the condition so when we write down that policy number in our temp table and then compare to it later we'll get all rows of 'A' back.

这篇关于如何使用EXISTS子句的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！