The following table provides equivalences for the ANY/ALL/SOME quantifiers, where op is IN or NOT IN.
| This usage … | Is equivalent to … |
|---|---|
| NOT (x op ALL (:a, :b, :c)) | x NOT op ANY (:a, :b, :c) |
| x NOT op SOME (:a, :b, :c) | |
| NOT (x op ANY (:a, :b, :c)) | x NOT op ALL (:a, :b, :c) |
| NOT (x op SOME (:a, :b, :c)) | |
| NOT (x op (:a, :b, :c)) | x NOT op (:a, :b, :c) |
In the equivalences, if op is NOT IN, then NOT op is IN, not NOT NOT IN.
Here are some examples.
| This expression … | Is equivalent to … |
|---|---|
| NOT (x IN ANY (:a, :b, :c)) | x NOT IN ALL (:a, :b, :c) |
| NOT (x IN ALL (:a, :b, :c)) | x NOT IN ANY (:a, :b, :c) |
| NOT (x NOT IN ANY (:a, :b, :c)) | x IN ALL (:a, :b, :c) |
| NOT (x NOT IN ALL (:a, :b, :c)) | x IN ANY (:a, :b, :c) |
| NOT (x IN (:a, :b, :c)) | x NOT IN (:a, :b, :c) |
| NOT (x NOT IN (:a, :b, :c)) | x IN (:a, :b, :c) |
Syntax 2: expression IN and NOT IN subquery
This syntax for IN and NOT IN is correct in either of the following two forms: