sql server - Why same EXISTS returns different result -

each policynumber can have multiple classcode. goal eliminate whole policynumber related columns including classcode's if @ least 1 classcode chosen eliminated. have cte1 policynumber , writtenpremium(wp) , have table tblclasscodesplazacommercial policynumber , classcode. in where clause im choosing:

where       exists (                     select  distinct policynumber                         tblclasscodesplazacommercial                        policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151)                     ) 

then if select * cte3 classcode =5151 still have policies classcode 5151

the entire select statement below:

 select                      cte1.policynumber,                 cte1.transactioneffectivedate,                 cc.classcode,                 case                     when row_number() on (partition cte1.quoteid, cte1.policynumber, cc.transactioneffectivedate order (select 0))=1 cte1.wp                      else 0                 end  wp--,            cte1 inner join tblclasscodesplazacommercial cc on cte1.policynumber=cc.policynumber , cte1.quoteid=cc.quoteid , cte1.transactioneffectivedate=cc.transactioneffectivedate            exists (select distinct policynumber tblclasscodesplazacommercial policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151)) 

but then, if declare @policiesthatdonthaveclasscodes table policynumber's dont have classcode 5151 , use in exists command - works.

declare @policiesthatdonthaveclasscodes table (policynumber varchar(100))  insert @policiesthatdonthaveclasscodes select distinct policynumber    tblclasscodesplazacommercial                                                                              policynumber not in (select  policynumber tblclasscodesplazacommercial  classcode =5151) select                   cte1.policynumber,             cte1.transactioneffectivedate,             cc.classcode,             case                 when row_number() on (partition cte1.quoteid, cte1.policynumber, cc.transactioneffectivedate order (select 0))=1 cte1.wp                  else 0             end  wp--,        cte1 inner join tblclasscodesplazacommercial cc on cte1.policynumber=cc.policynumber              , cte1.quoteid=cc.quoteid              , cte1.transactioneffectivedate=cc.transactioneffectivedate        exists (select * @policiesthatdonthaveclasscodes t  t.policynumber=cc.policynumber ) 

what difference? both sub-queries returns same result set. why in first case doesn't work, in second (when declared @policiesthatdonthaveclasscodes) - works? how can achieve same result without declaring table variables?

in first sample provide

where exists (select  distinct policynumber                     tblclasscodesplazacommercial                    policynumber not in (select  policynumber                                                 tblclasscodesplazacommercial                                                  classcode =5151)) 

there no correlation between outer query , subquery. evaluate subquery in isolation and, since query returns row, where exists … true.

in second example correlate subquery outer query, that's t.policynumber = cc.policynumber does, because cc outside subquery:

where exists (select * @policiesthatdonthaveclasscodes t                  t.policynumber=cc.policynumber ) 

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

thus 2 where clauses evaluate differently.

edit

possible reworking of first predicate

where not exists (select 1 tblclasscodesplazacommercial t                            t.policynumber = cc.policynumber                              , t.classcode = 5151)