Expansion of In-predicate

The in-predicate tests whether a value occurs in a specified set of values.
idmscu19
The in-predicate tests whether a value occurs in a specified set of values.
This article describes the following information:
2
2
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.
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');