This example shows the behavior of ANY/ALL/SOME.
Consider the following table definition and contents:
CREATE TABLE t (x INTEGER); INSERT t (1); INSERT t (2); INSERT t (3); INSERT t (4); INSERT t (5);
| IF you use this query … | THEN the result is … |
|---|---|
| SELECT * FROM t WHERE x IN ANY (1,2) | 1, 2 |
| SELECT * FROM t WHERE x = SOME (1,2) | 1, 2 |
| SELECT * FROM t WHERE x NOT IN ALL (1,2) | 3, 4, 5 |
| SELECT * FROM t WHERE NOT (x IN ANY (1,2)) | 3, 4, 5 |
| SELECT * FROM t WHERE NOT (x = SOME (1,2)) | 3, 4, 5 |
| SELECT * FROM t WHERE x NOT IN SOME (1, 2) | 1, 2, 3, 4, 5 |
| SELECT * FROM t WHERE x NOT = ANY (1, 2) | 1, 2, 3, 4, 5 |
| SELECT * FROM t WHERE x IN ALL (1,2) | no rows |
| SELECT * FROM t WHERE NOT (x NOT IN SOME (1,2)) | no rows |
| SELECT * FROM t WHERE x = ALL (1,2) | no rows |
| SELECT * FROM t WHERE NOT (x NOT = ANY (1,2)) | no rows |