NOT EXISTS and NOT IN IN Sql server

Question

Hi I'm not sure why it produce different result. could someone let me know what is my mistake?

this query return 2 rows of records

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod     
WHERE   Prod.intDepartmentRef =4  

bintProductRef
--------------------
164475
164476

and this query return 1 row of record

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod    
        WHERE  intCatRef = 132

bintProductRef
--------------------
164475

NOT EXISTS, when combine the above 2 queries, it should return 164476, but there is no ouput.

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod     
      WHERE   Prod.intDepartmentRef =4  
      AND NOT EXISTS (      
        SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod    
        WHERE  intCatRef = 132)

NOT IN, this return 1 row of data , which is 164476 and this is correct.

 SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod     
      WHERE  Prod.intDepartmentRef =4  
       AND Prod.bintProductRef NOT IN (      
        SELECT  Prod.bintProductRef FROM Prod_ProfileTbl Prod    
        WHERE  intCatRef = 132)

http://stackoverflow.com/questions/173041/not-in-vs-not-exists — vhadalgi, Oct 29 '13 at 06:41

score 1 · Accepted Answer · answered Oct 29 '13 at 06:39

In your "NOT EXISTS" you do not have any link between your main query and the sub query in the NOT EXISTS.

NOT EXISTS evaluates your subquery, it selects something, therefore "something" exists and your NOT EXISTS will be false and no rows are returned.

If you wish to use EXISTS you'll need to link the two querys together; something like this:

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod   
WHERE   intDepartmentRef =4  
AND NOT EXISTS (      
    SELECT 1 FROM Prod_ProfileTbl Prod2    
    WHERE  intCatRef = 132 AND prod.intCatRef = prod2.intCatRef)

, but for your purpose, the NOT IN or similar is the better approach.

score 0 · Answer 2 · answered Oct 29 '13 at 06:36

0

NOT EXISTS (...) means that it will only be true if whatever is in the brackets returns nothing. As you have shown it does return a row, hence NOT EXISTS is false, hence the AND is false and you won't get any rows returned.

answered Oct 29 '13 at 06:36

Dijkgraaf

11,049
17
42
54

score 0 · Answer 3 · answered Oct 29 '13 at 06:38

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod     
      WHERE   Prod.intDepartmentRef =4  
      AND NOT EXISTS (      
        SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod    
        WHERE  intCatRef = 132)

This part of code has a logic and it is than Prod.intDepartmentRef =4 and a query should not exists to return a result. note that two predicates should become true so the where clause become true . but also not that this query :

SELECT Prod.bintProductRef FROM Prod_ProfileTbl Prod    
            WHERE  intCatRef = 132

exists an have result so the second predicate become false and you have : true and false ---> false so there is no result to return.

NOT EXISTS and NOT IN IN Sql server

3 Answers3