CODEWITHSUNDEEP
sql
Jakub Dobrovolný
Jakub Dobrovolný

Reputation: 21

Different results using "in" and "not in" in SQL

I have this query:

SELECT 
    customerName, country
FROM 
    customers
WHERE 
    customernumber 
NOT IN
    (SELECT 
        customernumber
    FROM 
        payments
    WHERE 
        amount < 40000)
AND 
    customernumber 
IN
    (SELECT 
         customernumber
     FROM 
         payments
     GROUP BY
         customernumber
     HAVING 
         COUNT(amount) >=2 )

I have some result but when I change not in to in and < to >= I receive completely different results:

SELECT 
    customerName, country
FROM 
    customers
WHERE 
    customernumber 
IN
    (SELECT 
        customernumber
    FROM 
        payments
    WHERE 
        amount >= 40000)
AND 
    customernumber 
IN
    (SELECT 
         customernumber
     FROM 
         payments
     GROUP BY
         customernumber
     HAVING 
         COUNT(amount) >=2 )

I'm totally confused about this. Here you can see the schema: http://www.learntosql.com/retailer-database/.

Upvotes: 1

Views: 58

Answers (1)

JCollerton
JCollerton

Reputation: 3307

This part of the first query:

   (SELECT 
        customernumber
    FROM 
        payments
    WHERE 
        amount < 40000)

Selects all of the customers who have ever made a payment of less than 40,000. In the second query the equivalent part selects all of the customers who have ever made a payment of more than 40,000. As some customers will have made payments both over and under 40,000 this means that the two sets are not necessarily mutually exclusive (some people will be in both), therefore inverting IN and NOT IN and changing < for >= will not necessarily give the same result.

Upvotes: 2

Related Questions