If IN is used with a list of literals, the result is true if the value of expression_1 is:
- equal to any literal in the list,
- between signed_literal_1 and signed_literal_2, inclusively, when signed_literal_1 is less than or equal to signed_literal_2, or
- between signed_literal_2 and signed_literal_1, inclusively, when signed_literal_2 is less than signed_literal_1
If the value of expression_1 is null, then the result is considered to be unknown.
If the value of expression_1 is not null, and none of the conditions are satisfied for the result to be true, then the result is false.
Using this form, the IN search condition is satisfied if the expression is equal to any of the values in the list of literals; the NOT IN condition is satisfied if none of the values in the list of literals are equal to the expression.
| THE condition is true for this form … | WHEN … |
|---|---|
| expression_1 IN expression_2 | expression_1 = expression_2 |
| expression_1 NOT IN expression_2 | expression_1 <> expression_2 |
| expression_1 IN (const_1, const_2) | (expression_1 = const_1) OR (expression_1 = const_2) |
| expression_1 NOT IN (const_1, const_2) | (expression_1 <> const_1) AND (expression_1 <> const_2) |
|
expression_1 IN (signed_const_1 TO signed_const_2) where signed_const_1 <= signed_const_2 |
(signed_const_1 <= expression_1) AND (expression_1 <= signed_const_2) |
|
expression_1 IN (signed_const_1 TO signed_const_2) where signed_const_2 < signed_const_1 |
(signed_const_2 <= expression_1) AND (expression_1 <= signed_const_1) |
|
expression_1 NOT IN (signed_const_1 TO signed_const_2) where signed_const_1 <= signed_const_2 |
(expression_1 < signed_const_1) OR (expression_1 > signed_const_2) |
|
expression_1 NOT IN (signed_const_1 TO signed_const_2) where signed_const_2 < signed_const_1 |
(expression_1 < signed_const_2) OR (expression_1 > signed_const_1) |
Here are some examples.
| This statement … | Is equivalent to this statement … |
|---|---|
SELECT DeptNo FROM Department WHERE DeptNo IN (500, 600); |
SELECT DeptNo FROM Department WHERE DeptNo IN (500) OR (DeptNo = 600); |
UPDATE Employee SET Salary=Salary + 200 WHERE DeptNo NOT IN (100, 700); |
UPDATE Employee SET Salary=Salary + 200 WHERE (DeptNo ^= 100) AND (DeptNo ^= 700); |