CODEWITHSUNDEEP
mysqlsqldatabase
ubuntu_noob
ubuntu_noob

Reputation: 2365

Difference between "<>some" and "not in"

I was reading the books fundamentals of database systems and in the topic nested subqueries(set comparison) it was written that some and in are identical whereas <>some and not in are not. According to me <>some means "not at least one" and not in means "not in the set"...so I think they should mean the same.

Upvotes: 1

Views: 832

Answers (2)

Simo
Simo

Reputation: 953

They are not the same!

<>SOME means: not = a or not = b or not = c...

NOT IN means: not = a and not = b and not = c...

Hope this will be more clear for you now.

For example:

SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
    FROM Sales.SalesPerson);

This query return every single CustomerID with the exception of those for wich the TerritoryID is NULL.

While if you use NOT IN the query will return nothing.

Upvotes: 8

Ivien
Ivien

Reputation: 457

<> only apply to 1 value, while "not in" apply to 1 or more values, e.g.

1. WHERE id <> '123'
2. WHERE id NOT IN ('123') --same as case 1
3. WHERE id NOT IN ('123', '456')

Upvotes: 2

Related Questions