none
Not Exists using Select Values RRS feed

  • Question

  • Trying to utilize not exists, but not having any luck:

    Just want to return all customers except those that have custid in the Not Exist select value list. Something is wrong with my syntax: 

    SELECT 
     C.CustId
    ,C.Addresses
    	
    FROM Customers C
    WHERE CType in ('Cat1', 'Cat2')
          AND C.Status_of_Call <> 'closed'
    	  
    AND C.CustId not Exists (
     Select * from 
     (Values
    ('AB125'),
    ('CA152'),
    ('AW155'),
    ('AZ552')
    
    )x (SCID))


    Thursday, February 13, 2020 10:27 PM

Answers

  • create table Customers  (CType varchar(10), CustId varchar(10),Status_of_Call varchar(10) 
     ,Addresses  varchar(100))
    
     Insert into Customers(CType,CustId,Status_of_Call,Addresses  )
     values ('Cat1','AB125', 'open','Add81')
     ,('Cat1','some127', 'open','Add71')
     ,('Cat2','AZ552', 'closed','Add61')
     ,('Cat1','some125', 'open','Add51')
     ,('Cat2','some123', 'open','Add11')
    
     ,('Cat1','some123', 'closed','Add21')
     ,('Cat2','some345', 'closed','Add31')
    
    SELECT 
     C.CustId
    ,C.Addresses
    	
    FROM Customers C
    WHERE CType in ('Cat1', 'Cat2')
          AND C.Status_of_Call <> 'closed'	  
    AND  not Exists (
     Select 1 from 
     (Values('AB125'),('CA152'),('AW155'),('AZ552') )x (SCID) 
     where C.CustId=x.SCID )
    
    
    drop table Customers 

    • Marked as answer by vsdla Friday, February 14, 2020 12:40 AM
    Friday, February 14, 2020 12:31 AM
    Moderator

All replies

  • EXISTS returns TRUE if the subquery return at least one row, else it returns FALSE. Since you have an uncorrelated subquery, EXISTS in this case returns the same value for all rows in the outer table. And this the SELECT + VALUES returns rows, the value is TRUE, and NOT EXISTS yield FALSE.

    Normally, when you use EXISTS, you correlate the subquery with the outer table. But in this case it would be easier just to say:

    AND C.CustId not IN ('AB125', 'CA152', 'AW155', 'AZ552')
    
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, February 13, 2020 11:05 PM
  • Thank you, Erland. 

    I purposely wish to avoid Not In for performance purposes. The exception list is larger than specified in my example.

    Hence the preference for Not Exists

    Thursday, February 13, 2020 11:12 PM
  • Question
    You cannot vote on your own post
    0

    Thank you, Erland. 

    I purposely wish to avoid Not In for performance purposes. The exception list is larger than specified in my example.

    Hence the preference for Not Exists

    Thursday, February 13, 2020 11:23 PM
  • create table Customers  (CType varchar(10), CustId varchar(10),Status_of_Call varchar(10) 
     ,Addresses  varchar(100))
    
     Insert into Customers(CType,CustId,Status_of_Call,Addresses  )
     values ('Cat1','AB125', 'open','Add81')
     ,('Cat1','some127', 'open','Add71')
     ,('Cat2','AZ552', 'closed','Add61')
     ,('Cat1','some125', 'open','Add51')
     ,('Cat2','some123', 'open','Add11')
    
     ,('Cat1','some123', 'closed','Add21')
     ,('Cat2','some345', 'closed','Add31')
    
    SELECT 
     C.CustId
    ,C.Addresses
    	
    FROM Customers C
    WHERE CType in ('Cat1', 'Cat2')
          AND C.Status_of_Call <> 'closed'	  
    AND  not Exists (
     Select 1 from 
     (Values('AB125'),('CA152'),('AW155'),('AZ552') )x (SCID) 
     where C.CustId=x.SCID )
    
    
    drop table Customers 

    • Marked as answer by vsdla Friday, February 14, 2020 12:40 AM
    Friday, February 14, 2020 12:31 AM
    Moderator
  • Excellent. 

    Thank you!

    Friday, February 14, 2020 12:40 AM
  • I purposely wish to avoid Not In for performance purposes. The exception list is larger than specified in my example.

    Hence the preference for Not Exists

    There is no reason to assume that NOT IN wil be slower.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 14, 2020 10:50 PM