Why same EXISTS returns different result

Question

Each PolicyNumber can have multiple ClassCode. So the goal is to eliminate the whole PolicyNumber with all related columns including all ClassCode's if at least one ClassCode chosen to be eliminated. I have cte1 with PolicyNumber and WrittenPremium(WP) and I have table tblClassCodesPlazaCommercial with PolicyNumber and ClassCode. In my WHERE clause im choosing:

WHERE       EXISTS (
                    SELECT  DISTINCT PolicyNumber 
                    FROM    tblClassCodesPlazaCommercial 
                    WHERE   PolicyNumber NOT IN (SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial WHERE  ClassCode =5151)
                    )

Then if I select * from cte3 WHERE ClassCode =5151 I still have Policies with ClassCode 5151

The entire select statement below:

 select     
                cte1.PolicyNumber,
                cte1.TransactionEffectiveDate,
                cc.ClassCode,
                CASE
                    WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber, cc.TransactionEffectiveDate ORDER BY (SELECT 0))=1 THEN cte1.WP 
                    ELSE 0
                END  as WP--,
    from        cte1 inner join tblClassCodesPlazaCommercial cc on cte1.PolicyNumber=cc.PolicyNumber AND cte1.QuoteID=cc.QuoteID AND cte1.TransactionEffectiveDate=cc.TransactionEffectiveDate 
    where       EXISTS (SELECT DISTINCT PolicyNumber FROM tblClassCodesPlazaCommercial WHERE PolicyNumber NOT IN (SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial WHERE  ClassCode =5151))

But then, if I declare @PoliciesThatDontHaveClassCodes Table with PolicyNumber's that dont have ClassCode 5151 and use it in my EXISTS command - then it works.

DECLARE @PoliciesThatDontHaveClassCodes Table (PolicyNumber varchar(100)) 
INSERT INTO @PoliciesThatDontHaveClassCodes SELECT DISTINCT PolicyNumber    FROM tblClassCodesPlazaCommercial 
                                                                            WHERE PolicyNumber NOT IN (SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial WHERE  ClassCode =5151)
select      
            cte1.PolicyNumber,
            cte1.TransactionEffectiveDate,
            cc.ClassCode,
            CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber, cc.TransactionEffectiveDate ORDER BY (SELECT 0))=1 THEN cte1.WP 
                ELSE 0
            END  as WP--,
from        cte1 inner join tblClassCodesPlazaCommercial cc on cte1.PolicyNumber=cc.PolicyNumber 
            AND cte1.QuoteID=cc.QuoteID 
            AND cte1.TransactionEffectiveDate=cc.TransactionEffectiveDate 
where       EXISTS (SELECT * FROM @PoliciesThatDontHaveClassCodes t  WHERE t.PolicyNumber=cc.PolicyNumber )

What is the difference? the both sub-queries itself returns exactly the same result set. Why in first case it doesn't work, but in second (when I declared @PoliciesThatDontHaveClassCodes) - it works? How can I achieve same result without declaring any table variables?

but it's not the same. In one, you have a `NOT IN (SELECT....)` and the other has a different condition. The second query is actually relating the results from the `EXISTS` to the outer table, the first one is just seeing if there exists any row...and since it does exists, it's true for every result from the outer table — Lamak, Nov 17 '16 at 14:41

mendosi · Accepted Answer · 2016-11-17T14:59:10.453

In the first sample you provide

WHERE EXISTS (SELECT  DISTINCT PolicyNumber 
                FROM    tblClassCodesPlazaCommercial 
                WHERE   PolicyNumber NOT IN (SELECT  PolicyNumber 
                                               FROM tblClassCodesPlazaCommercial 
                                               WHERE  ClassCode =5151))

there is no correlation between the outer query and the subquery. Thus we evaluate the subquery in isolation and, since the query returns a row, then WHERE EXISTS … is always true.

In the second example you correlate the subquery with the outer query, that's what the t.PolicyNumber = cc.PolicyNumber does, because cc is outside the subquery:

WHERE EXISTS (SELECT * FROM @PoliciesThatDontHaveClassCodes t  
               WHERE t.PolicyNumber=cc.PolicyNumber )

so the result of the subquery is (potentially) different for every single row of the outer query.

Thus the two WHERE clauses evaluate differently.

Edit

Possible reworking of first predicate

WHERE NOT EXISTS (Select 1 From tblClassCodesPlazaCommercial As t
                           Where t.PolicyNumber = cc.PolicyNumber
                             And t.ClassCode = 5151)

Oh, got it. Thanks. So how would I rewrite the first one ? Is it possible? — Serdia, Nov 17 '16 at 14:52
@Oleg see updated answer using `LEFT ANTI SEMI JOIN` http://sqlity.net/en/1360/a-join-a-day-the-left-anti-semi-join/ — mendosi, Nov 17 '16 at 15:00

Why same EXISTS returns different result

1 Answers1