16.20 - Result - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K
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);