IN predicate

The IN predicate compares a value or values with a set of values.

When expression1 is specified, the IN predicate compares a value with a set of values. When fullselect1 is specified, the fullselect must return a single result column, and can return any number of values, whether null or not null. The data type of expression1 and the data type of the result column of fullselect1 or expression2 must be compatible. If expression is a single host variable, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables.

When a row-value-expression is specified, the IN predicate compares values with a collection of values. The result table of the fullselect2 must have the same number of columns as the row-value-expression. The data type of each expression in row-value-expression and the data type of its the corresponding result column of fullselect2 must be compatible. The value of each expression in row-value-expression is compared with the value of its corresponding result column of fullselect2. The value of the predicate depends on the operator that is specified:

When the operator is IN, the result of the predicate is:
- True if at least one row returned from fullselect2 is equal to the row-value-expression.
- False if the result of fullselect2 is empty or if no row returned from fullselect2 is equal to the row-value-expression.
- Otherwise, unknown (that is, if the comparison of row-value-expression to the row returned from fullselect2 evaluates to unknown because of a null value for at least one row returned from fullselect2 and no row returned from fullselect2 is equal to the row-value-expression).
When the operator is NOT IN, the result of the predicate is:
True if the result of fullselect2 is empty or if the row-value-expression is not equal to any of the rows returned by fullselect2.

False if the row-value-expression is equal to at least one row returned byfullselect2.

Otherwise, unknown (that is, if the comparison of row-value-expression to the row returned from fullselect2 evaluates to unknown because of a null value for at least one row returned from fullselect2 and the comparison of row-value-expression to the row returned from fullselect2 is not true for any row returned by the fullselect2).

The IN predicate is equivalent to the quantified predicate as follows:

Table 1. IN predicate and equivalent quantified predicates
IN predicate	Equivalent quantified predicate
`expression1 IN (expression2)`	`expression1 = expression2`
`expression IN (fullselect1)`	`expression = ANY (fullselect1)`
`expression NOT IN (fullselect1)`	`expression <> ALL (fullselect1)`
`expression1 IN (expressiona, expressionb, ...)`	`expression1 IN ( SELECT * FROM R)` When T is a table with a single row and R is a result table formed by the following fullselect: `SELECT value1 FROM T UNION SELECT value2 FROM T UNION . . . UNION SELECT valuen FROM T`
`row-value-expression IN (fullselect2)`	`row-value-expression = SOME (fullselect2)`
`row-value-expression IN (fullselect2)`	`row-value-expression = ANY (fullselect2)`
`row-value-expression NOT IN (fullselect2)`	`row-value-expression <> ALL (fullselect2)`

If the operands of the IN predicate have different data types or attributes, the rules that are used to determine the data type for evaluation of the IN predicate are those for UNION, EXCEPT, and INTERSECT. For a description, see Rules for result data types.

If the operands of the IN predicate are strings with different CCSIDs, the rules used to determine which operands are converted are those for operations that combine strings. See db2z_stringcomparisionsintro.html#db2z_charandgraphiccomparisions.

Notes

Accelerator-only support for more than 32K elements in an IN list

FL 503 The limit of 32767 (32K) elements in the IN list of an IN predicate can be exceeded, if all of the following conditions are true and the query runs on a V7 accelerator, which uses IBM® Db2 Warehouse to process SQL statements:

Query acceleration is enabled and requested for the query. For more information, see Enabling acceleration of SQL queries.
The application runs at Db2 application compatibility level V13R1M503 or higher.
Option 12 is specified in the list of values for the QUERY_ACCEL_OPTIONS subsystem parameter.
The target accelerator is IBM Db2 Analytics Accelerator V7 for z/OS®.
The IN list specifies only SQL constants.
The query can be functionally supported by the target accelerator and IBM Db2 Warehouse.

When such queries run on a V7 accelerator, the enforcement of any limits on the number of elements in the IN list predicate is handled by IBM Db2 Warehouse, instead of Db2 for z/OS. IBM Db2 Warehouse has no documented limit for elements in an IN list. However, practical limitations such as the memory and processing resources available for the query in IBM Db2 Warehouse are still likely to impose some limit on the number of elements that can be specified in the IN list predicate.

The following limitations also apply:

If the preceding conditions are not met when an IN predicate contains a list of more than 32K elements, the existing limit that is described in Limits in Db2 for z/OS applies, and Db2 for z/OS fails the query and issues SQL code -101.
If the query is successfully prepared to run on an accelerator, but fails at SQL OPEN query on the accelerator server (or on IBM Db2 Warehouse) for any reason, and the user requested QUERY ACCELERATION behavior ENABLE WITH FAILBACK, then query is not re-prepared and run in Db2 for z/OS. Instead, the OPEN query fails and the SQL error code returned by the accelerator or IBM Db2 Warehouse is returned to the user application.
If the query cannot be prepared to run on the accelerator for any reason, the PREPARE of the query fails with SQL code -4742 with a reason code value that indicates why the query cannot be accelerated.
If EXPLAIN output is requested for the PREPARE or bind, the DSN_QUERYINFO_TABLE table indicates that the query can only be run on an accelerator and the reason that the query cannot be run.

A view can also be created in Db2 for z/OS for a query with an IN list that contains more than 32K elements, but only under the same conditions listed previously for queries that contain such IN predicates. Any query that uses such a view is also evaluated and limited to the same conditions and restrictions described above. Such views also have the value 'R' in IBMREQD column in the SYSIBM.SYSVIEWS catalog table, to indicate the Db2 13 release dependency.

Examples

Example 1

The following predicate is true for any row whose employee is in department D11, B01, or C01.

  WORKDEPT IN ('D11', 'B01', 'C01')

Example 2

The following predicate is true for any row whose employee works in department E11.

  EMPNO IN (SELECT EMPNO FROM DSN8D10.EMP
    WHERE WORKDEPT = 'E11')

Example 3

The following predicate is true if the date that a project is estimated to start (PRENDATE) is within the next two years.

  YEAR(PRENDATE)  IN (YEAR(CURRENT DATE),
                      YEAR(CURRENT DATE + 1 YEAR),
                      YEAR(CURRENT DATE + 2 YEARS))

Example 4

The following example obtains the phone number of an employee in DSN8D10.EMP where the employee number (EMPNO) is a value specified within the COBOL structure defined below.

  77 PHNUM  PIC X(6).
  01 EMPNO-STRUCTURE.
     05 CHAR-ELEMENT-1  PIC X(6) VALUE '000140'.
     05 CHAR-ELEMENT-2  PIC X(6) VALUE '000340'.
     05 CHAR-ELEMENT-3  PIC X(6) VALUE '000220'.
     .
     .
     .
  EXEC SQL DECLARE PHCURS CURSOR FOR
       SELECT PHONENO FROM DSN8D10.EMP
         WHERE EMPNO IN 
         (:EMPNO-STRUCTURE.CHAR-ELEMENT-1,
          :EMPNO-STRUCTURE.CHAR-ELEMENT-2,
          :EMPNO-STRUCTURE.CHAR-ELEMENT-3)
  END-EXEC.
  EXEC SQL OPEN PHCURS
  END-EXEC.
  EXEC SQL FETCH PHCURS INTO :PHNUM
  END-EXEC.