5.11.7 Equivalent exchange for an IN predicate with row value constructors (addition of a condition for each row value constructor element)
When the row value constructor elements of the row value constructor on the left side of the IN predicate consist of only column specifications from the same table, equivalent exchange is performed by using an AND condition to add an IN predicate for each row value constructor element. However, IN predicates specified with the logical operators OR or NOT are not subject to equivalent exchange. Note that the maximum number of IN predicates that can be added by equivalent exchange is 255 for the entire SQL statement. Search conditions having more than 255 IN predicates will not be added.
During table retrieval using IN predicates added by this equivalent exchange, an index might be used.
The following shows examples of equivalent exchange. In the examples, C1, C2, and C3 are column names.
- Organization of this subsection
(1) Example 1
![[Figure]](GRAPHICS/F0000083.GIF)
- Explanation:
-
On the left side of the IN predicate, there is a row value constructor that specifies multiple column specifications from the same table. From this row value constructor, the same column specification is extracted and a condition with an IN predicate is added for each row value constructor element. During searches using conditions with IN predicates that were added after equivalent exchange, an index might be used.
(2) Format of conditions subject to equivalent exchange
The following shows the format of conditions that are subject to equivalent exchange:
-
IN predicate
row-value-constructor IN (row-value-constructor [,row-value-constructor]...)
The row value constructor on the left side of the IN predicate undergoes equivalent exchange when the following conditions are met:
-
All row value constructor elements are column specifications.
-
All column specifications of the row value constructor elements are from the same table.
-