# 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

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

Parameters

- value-expressionSpecifies the value to be compared to the set of values identified by the IN parameter. For expanded
syntax, see Expansion of Value-expression.value-expression - NOTReverses the test. NOT directs CA IDMS to test whether a value is not in the specified set of values.
- INIdentifies the set of values to which the value being tested is compared.
- value-expressionSpecifies a value that is a member of the set of test values.
may be enclosed in parentheses. Multiple occurrences ofValue-expressionmust be separated by commas and enclosed in parentheses.value-expression - subquerySpecifies 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
syntax, see Expansion of Subquery.subquery

Usage

Equivalence

**IN**

Value-expression

**is equivalent to a comparison predicate in the form**

value-expression

**=**

value-expression

**.**

value-expression

**IN (**

Value-expression

**) is equivalent to a quantified predicate in the form**

subquery

**= ANY (**

value-expression

**).**

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

*include NOT is:*not

- 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');