5.11.6 Equivalent exchange for an IN predicate with row value constructors (extraction of column specifications from the same table)
When the row value constructor on the left side of the IN predicate contains column specifications from multiple tables or contains a mix of column specifications and scalar operations, an equivalent exchange is performed to add IN predicates consisting of only column specifications from the same table as AND conditions. However, IN predicates specified with the logical operators OR or NOT are not subject to equivalent exchange.
With this equivalent exchange, it becomes possible to evaluate the IN predicate added before joining tables, which might reduce the input rows for the table join process. Furthermore, during table retrieval, 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/F0000082.GIF)
- Explanation:
-
On the left side of the IN predicate, there is a row value constructor that specifies column specifications from multiple tables and scalar operations. From this row value constructor, column specifications from the same table are extracted and conditions with IN predicates are added. 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:
-
The row value constructor elements include column specifications.
-
If all row value constructor elements are column specifications, column specifications from different tables are included.
-