This example shows the behavior of IN/NOT IN with a list of literals.
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 (1,2) | 1, 2 |
| SELECT * FROM t WHERE x IN ANY (1,2) | 1, 2 |
| SELECT * FROM t WHERE NOT (x NOT IN (1,2)) | 1, 2 |
| SELECT * FROM t WHERE x NOT IN (1,2) | 3, 4, 5 |
| SELECT * FROM t WHERE x NOT IN ALL (1,2) | 3, 4, 5 |
| SELECT * FROM t WHERE NOT (x IN (1, 2)) | 3, 4, 5 |
| SELECT * FROM t WHERE NOT (x IN ANY (1,2)) | 3, 4, 5 |
| SELECT * FROM t WHERE x IN (3 TO 5) | 3, 4, 5 |
| SELECT * FROM t WHERE x NOT IN SOME (1, 2) | 1, 2, 3, 4, 5 |
| SELECT * FROM t WHERE x IN (1, 2 TO 4, 5) | 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 NOT IN (1 TO 5) | no rows |