5.11.7 Equivalent exchange for search conditions in SQL statements that specify derived queries (transposition to the WHERE clause of a derived query)
Equivalent exchange is performed to move a search condition specified in the WHERE clause of an SQL statement that specifies a derived query to the WHERE clause of the derived query. For details about derived queries, see Derived queries and derived query names in the manual HADB SQL Reference.
The following shows examples of equivalent exchange. In the examples, C1, C2, and C3 are column names.
- Organization of this subsection
(1) Examples where equivalent exchange is performed
(a) Example 1 (when derived query is a query specification)
- Explanation:
-
The column specification on the left side of the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1"). This column is derived based on the selection expression ("T1"."C1") of the query specification in the derived query. Because the conditions for applying equivalent exchange are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.
(b) Example 2 (when derived query is a query expression)
- Explanation:
-
The column specification on the left side of the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1"). This column is derived based on the selection expression ("T1"."C1","T2"."C1") of the query expression in the derived query. Because the conditions for applying equivalent exchange are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.
(c) Example 3 (when derived query is a query specification)
- Explanation:
-
The column specification in the OR condition of the logical operation specified in the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1","D1"."C2","D1"."C3"). This column is derived based on the selection expression ("T1"."C1","T1"."C2","T1"."C3") of the query specification in the derived query. Because the OR condition of the logical operation satisfies the conditions for applying equivalent exchange, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.
(d) Example 4 (when derived query is a query expression)
- Explanation:
-
The column specification in the OR condition of the logical operation specified in the WHERE clause of the SQL statement that specifies the derived query is a derived column ("D1"."C1","D1"."C2","D1"."C3"). This column is derived based on the selection expression ("T1"."C1","T1"."C2","T1"."C3","T2"."C1","T2"."C2","T2"."C3") of the query expression in the derived query. Because the OR condition of the logical operation satisfies the conditions for applying equivalent exchange, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query.
(2) Format of conditions subject to equivalent exchange
The following shows the format of conditions that are subject to equivalent exchange:
-
Comparison predicate
▪ column-specification comparison-operator {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} ▪ {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} comparison-operator column-specification ▪ CONTAINS (column-specification,search-condition-expression-string) > 0
-
BETWEEN predicate
column-specification [NOT] BETWEEN {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} AND {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
-
IN predicate
column-specification [NOT] IN ({literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} [,{literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}]...)
-
LIKE predicate
column-specification [NOT] LIKE pattern-character-string [ESCAPE escape-character] pattern-character-string::={literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} escape-character::={literal|dynamic-parameter}
-
LIKE_REGEX predicate
column-specification [NOT] LIKE_REGEX regular-expression-character-string [FLAG {I|IGNORECASE}] regular-expression-character-string::=literal
-
NULL predicate
column-specification IS [NOT] NULL
(3) Notes
-
Equivalent exchange will be performed if the selection expression of the derived query on which the derived column specified in the column specification of the search condition is based is a column specification.
-
Equivalent exchange is not performed if a window function is specified in the derived query.
-
Equivalent exchange is not performed if the derived query is expanded into the SQL statement that specifies the derived query. For details about the expansion of derived queries, see Internal derived tables in the manual HADB SQL Reference.
-
Equivalent exchange is not performed for the following types of derived queries:
-
The derived query specifies the name of a viewed table, and the same viewed table name is specified more than once in the SQL statement.
-
The derived query specifies the name of a query specified as a WITH list element, and the same query name is specified more than once in the SQL statement.
-
Multiple WITH list elements are specified, and the derived query specifies a query name that is already specified as a WITH list element.
-
The derived query is specified in a joined table other than a joined table for which only INNER JOIN is specified its join type.
-
A table value constructor is specified as the derived query.
-
The derived query is a recursive query.
-
-
When all of the following conditions are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query:
-
A condition specified as an OR condition in a logical operation meets the format requirements for a search condition that is subject to equivalent exchange for search conditions in SQL statements that specify derived queries
-
All column specifications point to columns in the same derived table
-
-
When applying the following equivalent exchange to the OR condition of a logical operation, the following equivalent exchange is applied first. Then, equivalent exchange for search conditions in SQL statements that specify derived queries is applied.
-
Equivalent exchange that moves a condition from inside the OR condition to outside the OR condition
-
Equivalent exchange that adds the IN condition created from an = condition in the OR condition to the outside of the OR condition
For details about equivalent exchange described earlier, see 5.11.1 Equivalent exchange for OR conditions (removing from the OR conditions) and 5.11.2 Equivalent exchange for OR conditions (converting to IN conditions).
-
-
Conditions specified in NOT conditions in logical operations and conditions that contain subqueries are not subject to equivalent exchange.
-
Equivalent exchange is not performed if the columns specified by column-specification are external reference columns. For details about external reference columns, see Specification format and rules for subqueries in the manual HADB SQL Reference.
-
If only literals are specified in a scalar operation, that scalar operation might be treated as a literal. For details about the conditions under which scalar operations are equivalent to literals, see Specification format and rules for value expressions in the manual HADB SQL Reference.