CODEWITHSUNDEEP
t-sqlsql-server-2005
tsohtan
tsohtan

Reputation: 850

NOT EXISTS and NOT IN IN Sql server

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)

Upvotes: 0

Views: 71

Answers (3)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

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.

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

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.

Upvotes: 0

Dijkgraaf
Dijkgraaf

Reputation: 11527

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.

Upvotes: 0

Related Questions