2.10.2.5.11. JPQL In Expressions

The syntax for the use of the comparison operator [ NOT ] IN in a conditional expression is as follows:

  • in_expression ::= state_field_path_expression [NOT] IN {( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }

  • in_item ::= literal | single_valued_input_parameter

The state_field_path_expression must have a string, numeric, date, time, timestamp, or enum value.

The literal and/or input_parameter values must be like the same abstract schema type of the state_field_path_expression in type. (See JPQL Equality and Comparison Semantics ).

The results of the subquery must be like the same abstract schema type of the state_field_path_expression in type. Subqueries are discussed in JPQL Subqueries.

Examples:

o.country IN ('UK', 'US', 'France')

is true for UK and false for Peru, and is equivalent to the expression:

(o.country = 'UK') OR (o.country = 'US') OR (o.country = ' France')

In the following expression:

o.country NOT IN ('UK', 'US', 'France')

is false for UK and true for Peru, and is equivalent to the expression:

NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'France'))

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If the value of a state_field_path_expression or in_item in an IN or NOT IN expression is NULL or unknown, the value of the expression is unknown.

Note that use of a collection-valued input parameter will mean that a static query cannot be precompiled.