# 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`.