7.30.3 Rules for derived table expansion

When you execute a derived query to generate a derived table, it expands the outer query expression body into an equivalent form that does not contain the derived table. This is called a derived table expansion. The following examples illustrate derived table expansions.

Example of a query that uses a derived table

SELECT "PN1","PR2"*1.05 AS "TXPRICE"
   FROM (SELECT "PNAME","PRICE","PLACE" FROM "STOCK"
         WHERE "PRICE">10000)
      AS "X"("PN1","PR2","PL3")
         WHERE "PL3" IN('Alaska','Arizona')

Example of derived table expansion

SELECT "PNAME" AS "PN1","PRICE"*1.05 AS "TXPRICE" 
   FROM "STOCK" 
      WHERE "PRICE">10000 AND "PLACE" IN('Alaska','Arizona')

The following shows the expansion rules for derived tables:

No internal derived table is expanded when all of the following conditions are met:
- The same viewed table is specified more than once in a single SQL statement.
- Any of the following items were specified in the query expression body when the viewed table was defined:
  - SELECT DISTINCT
  - Set operations
  - Subquery
  - Comma join
  - Joined table
  - Viewed table
  - Query name
  - Set function
  - Window function
  - GROUP BY clause
  - HAVING clause
  - Table function derived table
  - Archivable multi-chunk table
  - WHERE clause
Similarly, no internal derived table is expanded in the following case: the same query name is specified more than once in a single SQL statement, and any of the preceding items are specified within the query expression body specified for a WITH list element of that query name.
If a recursive query is specified in a WITH list element, the following rule applies: If a viewed table or query name is specified as a recursive member of the recursive query, no internal derived table is expanded for the viewed table or query name.
If a recursive query is included in a WITH list element, no internal derived table is expanded for the query name of the WITH list element.
No internal derived table for the query name of a WITH list element is expanded if the list element satisfies either Condition 1 or Condition 2 shown later. Also, in a certain case, a derived table that is derived from a viewed table defined by the CREATE VIEW statement is not expanded. The case is when the WITH clause is specified in the query expression in the CREATE VIEW statement and the query name of a WITH list element that satisfies either of the following conditions is referenced from that query expression:

Condition 1:

All of the following conditions are satisfied:
- Multiple WITH list elements are specified.
- Any of the following items are specified in the query expression bodies specified in WITH list elements:
  - SELECT DISTINCT
  - Set operations
  - Subquery
  - Comma join
  - Joined table
  - Viewed table
  - Query name
  - Set function
  - Window function
  - GROUP BY clause
  - HAVING clause
  - Table function derived table
  - Archivable multi-chunk table
- Any of the following conditions are satisfied:
  - The query name that corresponds to a WITH list element is specified at two or more locations in the SQL statement.
  - The query name that corresponds to a WITH list element is specified at one location in the SQL statement, and the query name is specified in a subquery that satisfies either of the following conditions:
    
    [1] Of the subqueries that are specified in a query specification that includes a table reference to which an external reference column belongs, a subquery that includes a subquery in which that external reference column is specified
    
    [2] A subquery that is included in the subquery in [1]
    
    Example of where the query name Q1 satisfies condition [2]:
    
    In the preceding example, query B is a subquery that satisfies condition [1]. Queries C to E are a subquery that satisfies condition [2]. Query F is a subquery that satisfies neither condition [1] nor condition [2]. The query name Q1 satisfies condition [2] because it is specified in query C.
Condition 2:

All of the following conditions are satisfied:
- A WITH list element is specified.
- The same viewed table is specified multiple times in a query expression body that is specified in the WITH list element.
  
  Alternatively, the query name (other than a WITH clause) that corresponds to the WITH list element is specified multiple times.
- The query name that corresponds to the WITH list element is specified in either of the following subqueries:
  
  [1] Of the subqueries that are specified in a query specification that includes a table reference to which an external reference column belongs, a subquery that includes a subquery in which that external reference column is specified
  
  [2] A subquery that is included in the subquery in [1]
The following derived tables are not expanded:
- Internal derived table equivalently exchanged from a dictionary table or system table that is specified in the query expression in a CREATE VIEW statement
- Derived table that is derived by a table value constructor
- Derived table that is equivalently exchanged by the FULL OUTER JOIN specification
- Derived table that is equivalently exchanged from an archivable multi-chunk table
  
  For details about a derived table that is equivalently exchanged from an archivable multi-chunk table, see Equivalent exchange of SQL statements that search archivable multi-chunk tables in the HADB Application Development Guide.
- Derived table that is equivalently exchanged by the specification of the OR condition
  
  For details about a derived table that is equivalently exchanged by the specification of the OR condition, see Equivalent exchange for OR conditions (equivalent exchange to derived tables for which the UNION ALL set operation is specified) in the HADB Application Development Guide.

To Page Top