Expansion of In-predicate

Predicates and Search Condition › Expansion of In-predicate

Expansion of In-predicate

The in-predicate tests whether a value occurs in a specified set of values.

Syntax

Expansion of in-predicate

►►─── value-expression ─┬───────┬─ IN ────────────────────────────────────────►
                        └─ NOT ─┘

           ┌───────── , ────────┐
 ►─┬─ ( ─┬─▼─ value-expression ─┴─┬─ ) ─┬─────────────────────────────────────►◄
   │     └─ subquery ─────────────┘     │
   │                                    │
   └─ value-expression ─────────────────┘

Parameters

value-expression

Specifies the value to be compared to the set of values identified by the IN parameter. For expanded value-expression syntax, see Expansion of Value-expression.

NOT

Reverses the test. NOT directs CA IDMS to test whether a value is not in the specified set of values.

IN

Identifies the set of values to which the value being tested is compared.

value-expression

Specifies a value that is a member of the set of test values.

Value-expression may be enclosed in parentheses. Multiple occurrences of value-expression must be separated by commas and enclosed in parentheses.

subquery

Specifies a subquery that returns zero or more rows and whose result table consists of a single column. The column values are members of the set of test values. For expanded subquery syntax, see Expansion of Subquery.

Usage

Equivalence

Value-expression IN value-expression is equivalent to a comparison predicate in the form value-expression = value-expression.

Value-expression IN (subquery) is equivalent to a quantified predicate in the form value-expression = ANY (subquery).

Comparable Data Types

The data types of the values in an IN predicate must be comparable.

Note: For more information about comparing values of different data types, see Comparison, Assignment, Arithmetic, and Concatenation Operations.

Truth Value of an IN Predicate without NOT

The result of an IN predicate that does not include NOT is:

True when the value being tested is equal to at least one of the values in the test set
False when the value being tested is not equal to any of the values in the test set or when the result of the subquery is an empty set
Unknown when the value being tested is null or when values in the test set are a combination of null value and values not equal to the value being tested

This table presents examples of results of IN predicates without NOT:

Predicate	Result
'A' IN ('A','B')	True
'A' IN ('B')	False
'A' IN (null-value)	Unknown
'A' IN ('A',null-value)	True
'A' IN ('B',null-value)	Unknown

Truth Value of an IN Predicate with NOT

The result of an IN predicate that includes NOT is:

True when the value being tested is not equal to any of the values in the test set or when the result of the subquery is an empty set
False when the value being tested is equal to at least one of the values in the test set
Unknown when the value being tested is null or values in the test set are a combination of null value and values not equal to the value being tested

This table presents examples of results of IN predicates with NOT:

Predicate	Result
'A' NOT IN ('A','B')	False
'A' NOT IN ('B')	True
'A' NOT IN (null-value)	Unknown
'A' NOT IN ('A',null-value)	False
'A' NOT IN ('B',null-value)	Unknown

Example

As the Search Condition in a WHERE Parameter

The following SELECT statement identifies employees who live in one of four specified cities:

select emp_fname, emp_lname, dept_id
   from employee
   where emp_city in ('Newton','Wellesley','Natick','Wayland');