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

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. 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").

• Perhaps highlighting the difference in English between "sid is not equal to any <...>" and "there is any <...> that sid is not equal to" helps too.
– hvd
Jul 10 '16 at 14:35

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.