7.30.1 Examples of using internal derived tables
When you execute a query on a viewed table, HADB derives an internal table based on the CREATE VIEW statement specification that it codes in the FROM clause that specifies the viewed table. This derived table is called an internal derived table.
Similarly, an internal derived table is also used when you execute a query using a query name specified in a WITH list element.
The following examples illustrate how internal derived tables are used.
- Organization of this subsection
(1) Example 1: Executing a query on a viewed table
When you execute a query on a viewed table, an internal derived table is used in the FROM clause that specifies the viewed table. This is illustrated in the following examples.
- Viewed table definition:
-
CREATE VIEW "V1" ("VC1","VC2") AS SELECT * FROM "T1" WHERE "C1">100 - SELECT statement to be executed:
-
SELECT * FROM "V1"
When the SELECT statement shown above is executed, the following internal derived table is used.
- Internal derived table that is used:
-
SELECT * FROM (SELECT * FROM "T1" WHERE "C1">100) AS "V1" ("VC1","VC2")The underlined portion indicates the internal derived table.
(2) Example 2: Executing a query using a query name in a WITH clause
When you execute a query using a query name in a WITH clause, an internal derived table is used in the FROM clause that specifies the query name. This is illustrated in the following example.
- SELECT statement to be executed:
-
WITH "Q1"("QC1","QC2") AS (SELECT * FROM "T1" WHERE "C1">100) SELECT * FROM "Q1"Here, Q1 is the query name, and the underlined portion indicates the query expression body specified in the WITH clause. When the SELECT statement shown above is executed, the following internal derived table is used.
- Internal derived table that is used:
-
SELECT * FROM (SELECT * FROM "T1" WHERE "C1">100) AS "Q1" ("QC1","QC2")The underlined portion indicates the internal derived table.