2.21.1 Conditions for an inner derived table
(1) Named derived table that is derived by specifying SELECT DISTINCT
A named derived table is included in the subquery. Alternatively, the query specification, specifying a named derived table in a FROM clause, directly contains one of the following items:
- GROUP BY clause, HAVING clause, or a set function
- SELECT DISTINCT
- Table joining (including outer joins and inner joins)
- A value expression other than a column specification is specified in the selection expression.
- A scalar subquery is specified in the selection expression.
- The selection expression does not specify all the columns, column by column, in the view table specified in the FROM clause.
- ORDER BY clause
- Examples
If named derived table V1 (a view table) and Q1 (the query name in the WITH clause) that are derived by SELECT DISTINCT C1,C2 FROM T1 are specified in the FROM clause, the following coding creates an internally derived table with respect to V1:
- Example 1:
SELECT * FROM T2 WHERE EXISTS(SELECT * FROM V1)
- Example 2:
SELECT VC1,VC2 FROM V1 GROUP BY VC1,VC2
- Example 3:
WITH Q1(QC1,QC2) AS (SELECT DISTINCT C1,C2 FROM T1)
SELECT DISTINCT * FROM V1
- Example 4:
SELECT X.VC1,Y.C1 FROM V1 X,T2 Y WHERE X.VC1=Y.C2
- Example 5:
SELECT V1.VC1,T2.C2 FROM V1 LEFT JOIN T2 ON T2.C2=V1.VC2
- Example 6:
WITH Q1(QC1,QC2) AS (SELECT DISTINCT C1,C2 FROM T1)
SELECT QC1+100,CURRENT_DATE FROM Q1
(2) Named derived table that is derived by specifying a GROUP BY clause, HAVING clause, or set function
The query specification specifying a named derived table in a FROM clause directly contains one of the following items:
- GROUP BY clause, HAVING clause, or a set function
- Table joining (including outer joins and inner joins)
- Window function
- Examples
If named derived table V1 (a view table) and Q1 (the query name in the WITH clause) that are derived by SELECT C1,C2 FROM T1 GROUP BY C1,C2 and named derived table V2 (a view table) that is derived by SELECT MAX(C1),C2 FROM T1 GROUP BY C2 HAVING C2<100 are specified in the FROM clause, the following coding creates an internally derived table with respect to V1, V2 and Q2:
- Example 1:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1
GROUP BY C1,C2) SELECT AVG(QC1),QC2 FROM Q1
GROUP BY QC2
- Example 2:
SELECT V1.VC1,V2.VC1 FROM V1,V2 WHERE V1.VC1=V2.VC1
- Example 3:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1
GROUP BY C1,C2)
SELECT Q1.QC1,V1.VC1 FROM Q1 INNER JOIN V1 ON
V1.VC2=Q1.QC2
(3) Named derived table that is derived by specifying a value expression other than a column specification in a selection expression
A query specification specifying a named derived table in a FROM clause directly contains one of the following items:
- GROUP BY clause, HAVING clause, or a set function#
- Window function
- Outer or inner join
- #
- Excludes cases in which the rapid grouping facility is enabled. For details about the rapid grouping facility, see the manual HiRDB Version 8 UAP Development Guide.
- Examples
If named derived table V1 (a view table) and Q1 (the query name in the WITH clause) that are derived by SELECT C1+100, C2| |C2 FROM T1 are specified in the FROM clause, the following coding creates an internally derived table with respect to V1 and Q1:
- Example 1:
SELECT AVG(VC1),VC2 FROM V1 GROUP BY VC2
- Example 2:
SELECT * FROM V1 LEFT JOIN T2 ON T2.C2=V1.VC2
- Example 3:
WITH Q1(QC1,QC2) AS (SELECT C1+100,C2| |C2 FROM T1)
SELECT QC1,QC2 FROM Q1 GROUP BY QC1,QC2
HAVING QC1<=100
- Example 4:
SELECT COUNT(*) OVER(),VC1 FROM V1
(4) Named derived table that is derived by specifying a set function specifying a DISTINCT specification
The query specification, specifying a named derived table in a FROM clause, directly contains one of the following items:
- SELECT DISTINCT
- GROUP BY clause, HAVING clause, or set function
- Joined table (including inner or outer joins)
- Window function
- Example
If named derived table V1 (a view table) that is derived by SELECT AVG (DISTINCT C1) FROM T1 is specified in the FROM clause, the following coding creates an internally derived table with respect to V1:
SELECT DISTINCT VC1 FROM V1
WITH Q1(C1) AS (SELECT AVG(DISTINCT C1) FROM T1)
SELECT COUNT(*) OVER(),C1 FROM Q1
(5) Named derived table that is derived by specifying a joined table
A named derived table is specified in an inner or outer join table reference.
- Examples
If named derived table V1 (a view table) and Q1 (the query name in the WITH clause) that are derived by SELECT T1.C1,T2.C1 FROM T1,T2 are specified in the FROM clause, the following coding creates an internally derived table with respect to V1 and Q1:
- Example 1:
SELECT V1.* FROM V1 LEFT JOIN T3 ON T3.C1=V1.VC1
- Example 2:
WITH Q1(QC1,QC2) AS (SELECT T1.C1,T2.C1 FROM T1,T2
SELECT * FROM Q1 INNER JOIN T3 ON Q1.QC1=T3.C1
(6) Named derived table that is derived by specifying an inner or outer join
The query specification specifying a named derived table in a FROM clause directly contains a table join.
- Examples
- In the specification of named derived tables V1 (a view table) and Q1 (a query name) derived by SELECT T1.C1,T2.C1 FROM T1 LEFT JOIN T2 ON T1.C2=T2.C2 in a FROM clause, the following coding creates an inner derived table with respect to V1 and Q1:
- Example 1:
- SELECT V1.VC1,T3.C1 FROM V1 LEFT JOIN T3 ON T3.C2=V1.VC2
- Example 2:
- WITH Q1(QC1,QC2) AS (SELECT T1.C1,T2.C1 FROM T1 LEFT JOIN T2
- ON T1.C2=T2.C2) SELECT Q1.QC1 FROM Q1 INNER JOIN T3 ON T3.C2=Q1.QC2
(7) Named derived table that is derived by specifying a value expression, containing a subquery, in a selection expression
A query specification specifying a named derived table in a FROM clause directly contains one of the following items:
- SELECT DISTINCT
- GROUP BY clause, HAVING clause, or a set function
- Joined table (including inner or outer joins)
- A value expression, other than a column specification, in a selection expression
- A scalar subquery specification in a selection expression
- Same column derived from a value expression containing a subquery specified two or more times in the selection expression of a named derived table
- A column derived from a value expression containing a subquery specified in the selection expression of a named derived table as a column that references outside
- View table defined before version 07-02
- Examples
- In the specification of named derived tables V1 (a view table) and Q1 (a query name) derived by SELECT (SELECT C1 FROM T2),C1 FROM T1 in a FROM clause, the following coding creates an inner derived table with respect to V1 and Q1:
- Example 1:
SELECT VC1,VC2 FROM V1 WHERE VC1>0
- Example 2:
WITH Q1(QC1,QC2) AS (SELECT (SELECT C1 FROM T2),C1 FROM T1)
SELECT QC1,QC2 FROM Q1 WHERE QC1>0
(8) Named derived table that is derived by set operations
One of the following conditions must be satisfied:
- One of the operands in the set operation directly contains one of the following items:
- A query on an inner derived table
- A query specifying a derived table
- A scalar subquery in a selection expression
- One of the operands in the set operation and a query on the named derived table satisfy one of the conditions listed in (1)-(8).
- Condition (9) or (10) is satisfied depending on whether a specified set operation contains options other than UNION ALL or it contains the option UNION ALL only.
- Example
- In the specification of named derived tables V1 (a view table) and Q1 (a query name) derived by SELECT (SELECT C1 FROM T2),C2 FROM T1 UNION SELECT C1,C2 FROM T3 in a FROM clause, the following coding creates an inner derived table with respect to V1 and Q1:
- Example 1:
SELECT * FROM V1
- Example 2:
WITH Q1(QC1,QC2) AS (
SELECT (SELECT C1 FROM T2),C2 FROM T1 UNION SELECT C1,C2 FROM T3)
SELECT * FROM Q1
(9) Named derived table that is derived by a set operation containing options other than UNION ALL
One of the following conditions must be satisfied:
- The query specification specifying a named derived table in the FROM clause directly contains one of the following items:
- GROUP BY clause, HAVING clause, or a set function
- SELECT DISTINCT
- Table joining (including inner and outer joins)
- WHERE clause
- Subquery
- A value expression, other than a column specification, in a selection expression
- Not all columns in the named derived table are specified once per selection expression
- The condition (8) Named derived table that is derived by set operations is satisfied.
- Examples
- In the specification of named derived tables V1 (a view table) and Q1 (a query name) derived by SELECT C1,C2 FROM T1 UNION SELECT C1,C2 FROM T2 in a FROM clause, the following coding creates an inner derived table with respect to V1 and Q1:
- Example 1:
- SELECT C1,C2 FROM V1 GROUP BY C1,C2
- Example 2:
- WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1 UNION SELECT C1,C2 FROM T2)
- SELECT QC1,QC2 FROM Q1,T3 WHERE QC1=T3.C1
(10) Named derived table that is derived by a set operation specifying UNION ALL only
One of the following conditions must be satisfied:
- The query specification specifying a named derived table in the FROM clause directly contains one of the following items:
- GROUP BY clause, HAVING clause, set function
- Window function
- WHERE clause, subquery (provided that the query in (1) is included in one of the following: a subquery, an operand in a set operation, or the query body of an INSERT statement)
- Function call
- System-defined scalar function
- Component specification
- WRITE specification
- GET_JAVA_STORED_ROUTINE_SOURCE specification
- A column not contained in a selection item is specified as a sort item
- A subquery specifying the named derived table meeting Condition (2) in a FROM clause
- A subquery specifying a derived table
- A subquery specifying a value expression, other than a column specification, in a GROUP BY clause
- One of the SQL variables or SQL parameters specified in a selection expression takes one of the following data types:
BLOB type
BINARY with a minimum definition length of 32,001 bytes
Abstract data type
BOOLEAN type
- One of the following items is specified for the query specification specifying a named derived table for joining tables:
- A named derived table specified for referencing a table other than a foreign table on the farthest left of an outer join
- Comma join specified for a FROM clause in which a named derived table is specified (that is, another table reference is specified other than the joined table specifying the named derived table)
- A subquery or derived table
- Query specification contained in a subquery, or the operation term of a set operation
- A set operation term for deriving a named derived table contains one of the following items:
Table joining
GROUP BY clause, HAVING clause, set function
SELECT DISTINCT
A value expression, other than a column specification, in a selection expression
Query that generates an internal derived table
Query specifying a derived table
- A named derived table derived by specifying a set operation specified in addition to another named derived table
- One of the following items is specified for a table reference for a joined table for which a named derived table is specified:
A named derived table derived by specifying table joining
A named derived table derived by specifying a GROUP BY clause, HAVING clause, or set function
A named derived table derived by specifying SELECT DISTINCT
A named derived table derived by specifying a value expression other than a column specification for the selection expression
A named derived table derived by specifying a query that generates an internal derived table
A named derived table derived by specifying a subquery
- The total number of tables obtained from the following expression exceeds 65:
total-number-of-tables = (aggregate-number-of-tables-from-which-named-derived-tables-are-derived)
+ (number-of-set-operations-for-deriving-named-derived-tables + 1)
(aggregate-number-of-tables-to-be-specified-on-the-right-side-of-outer-join)
+ (aggregate-number-of-tables-specified-for-query-if-query-is-specified-in-addition-to-query-specifying-named-derived-table)
- Satisfies the conditions described in (8) Named derived table that is derived by set operations.
- Examples
- In the specification of named derived tables V1 (a view table) and Q1 (a query name) derived by SELECT C1,C2 FROM T1 UNION ALL SELECT C1,C2 FROM T2 in a FROM clause, the following coding creates an inner derived table with respect to V1 and Q1:
- Example 1:
- SELECT C1,C2 FROM V1 GROUP BY C1,C2
- Example 2:
- WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1 UNION ALL SELECT C1,C2 FROM T2)
- SELECT QC1,QC2 FROM Q1,T3 WHERE QC1=T3.C1
- Example 3:
- SELECT * FROM T1 WHERE EXISTS(SELECT * FROM V1 WHERE V1.C1=T1.C1)
- Example 4:
- INSERT INTO T3 SELECT * FROM V1 WHERE C1>'C001'