Difference between "<> ANY(…)" and "NOT = ANY(…)"?

Question

Could you please explain the difference between these two operators?

sID != smth and not sID = smth.

At a first glance they seem perfectly equal. But they give different results.

Answer 1

NOT sid = ANY (SELECT ...) 

is equivalent to:

sid <> ALL (SELECT ...)  

So, your two conditions are not the same.

---

ALL and ANY operators are hard for me (and many people) to use. I think that's how many prefer to use IN, NOT IN, EXISTS and NOT EXISTS which result in more self-explanatory code.

If you do want to work with them, think that sid = ANY (SELECT ...) means "check if sid is equal to any (some) of the (select...) values".

Then the NOT sid = ANY (SELECT ...) is the opposite of that. But the opposite of "equal to any of them" is "different to all of them" (and not "different to some of them").

Answer 2

This is what the SQL Standard has to say about ANY

<quantified comparison predicate> ::= <row value constructor> <comp op> <quantifier> <table subquery>

<quantifier> ::= <all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

General Rules

1) Let R be the result of the <row value constructor> and let T be the result of the <table subquery>.

2) The result of "R <comp op> <quantifier> T" is derived by the application of the implied <comparison predicate> "R <comp op> RT" to every row RT in T:

Case:

a) If T is empty or if the implied <comparison predicate> is true for every row RT in T, then "R <comp op> <all> T" is true.

b) If the implied <comparison predicate> is false for at least one row RT in T, then "R <comp op> <all> T" is false.

c) If the implied <comparison predicate> is true for at least one row RT in T, then "R <comp op> <some> T" is true.

d) If T is empty or if the implied <comparison predicate> is false for every row RT in T, then "R <comp op> <some> T" is false.

e) If "R <comp op> <quantifier> T" is neither true nor false, then it is unknown.

Consider the case that sID = 1 and the sub query returns 1,2,3.

The result of the comparisons is

+---+-------+-------+
|   |   =   |  <>   |
+---+-------+-------+
| 1 | TRUE  | FALSE |
| 2 | FALSE | TRUE  |
| 3 | FALSE | TRUE  |
+---+-------+-------+

So = ANY returns true because it checks that there is at least one row =1.

TRUE OR FALSE OR FALSE = TRUE

Conversely <> ANY also returns true because it checks that there is at least one row <> 1.

FALSE OR TRUE OR TRUE = TRUE

Then you negate the <> ANY so it becomes false.