Hitachi

Hitachi Advanced Database SQL Reference

7.33.5 Summary of when derived table expansion is performed

The following table summarizes the conditions under which derived table expansion is and is not performed.

Table 7‒53: Summary of when derived table expansion is performed (1/3)

Specification of the SQL statement that manipulates a derived query

Specification of the derived query

SEL_

DIST

GRP

GRP_EXP

SEL_EXP

SEL_

NCOL

SEL_RAND

SEL_

RANDCRS

SEL_

RANDROW

SEL_PRM

SEL_DIST

Y

Y

Y

Y

Y

Y#5

Y

Y

N

GRP

N

Y#6

Y#6

Y#2

Y#2

Y#5

D

D

N

GRP_EXP

N

N

N

Y

Y

Y#5

D

D

N

A-FUNC

N

Y

#6, #7

Y

#6, #7

Y

Y

Y#5

Y#8

D

N

D-FUNC

N

N

N

Y

Y

Y#5

Y#8

D

N

I-FUNC2

N

N

N

D

D

Y#5

Y#8

D

N

WIN_AGG

N

Y

Y

Y

Y

Y#5

Y#8

D

N

WIN_PAR

N

Y

Y

Y

D

Y#5

Y#8

D

N

WIN_ORD

N

Y

Y

Y

Y

Y#5

Y#8

D

N

SEL_EXP

Y

Y

Y

Y

Y

Y#5

Y

Y#9

N

SEL_RAND

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_RANDROW

N

Y

Y

Y

Y

Y#5

Y

Y#9

N

SEL_SUBQ

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_WINDOW

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_CNDRV

N

Y

Y

Y

Y

Y#5

Y

Y

N

SEL_NCNDRV

Y#11

Y

Y

Y

Y

Y#5

Y

Y

N

JOIN

N

N

N

Y

Y

Y#5

Y

Y#5

N

IN_J_TBL

N

N

N

Y#4

N

Y#5

Y

Y#5

N

J_TBL#12

N

N

N

Y

Y

Y#5

Y

Y#5

N

FJ_TBL

N

N

N

N

N

N

N

N

N

TBLF_TBL

N

N

N

N

N

N

N

N

N

ANY_ARRAY_ELMREF

Y

Y

Y

Y

Y

Y#5

Y

Y

N

RVC

Y

N

N

Y#16

Y

Y#5

Y

Y

N

S_KEY

Y

Y

Y

Y

Y

Y#5

Y

Y

N

SEXP_KEY

N

Y

Y

Y

Y

Y#5

Y

Y#9

N

O_REF

Y

Y

D

Y#17, #18

Y

Y#5

D

D

N

O_REF_FUNC

N

N

N

D

D

Y#5

D

D

N

Other items

Y

Y

Y

Y

Y

Y#5

Y#8

Y

#8, #9

N
Table 7‒54: Summary of when derived table expansion is performed (2/3)

Specification of the SQL statement that manipulates a derived query

Specification of the derived query

SUBQ

SEL_

IFN

FUNC_

COL

FUNC_

EXP

WINDOW

JOIN

CJOIN#12

WHERE_

CE

LIMIT

SEL_DIST

N

N

Y

Y

N

Y

Y

Y

N

GRP

N

N

D

D

N

Y

Y

Y

N

GRP_EXP

N

N

D

D

N

Y

Y

Y

N

A-FUNC

N

N

Y#7

Y#7

N

Y

Y

Y

N

D-FUNC

N

N

D

D

N

Y

Y

Y

N

I-FUNC2

N

N

D

D

N

Y

Y

Y

N

WIN_AGG

N

N

Y

Y

N

Y

Y

Y

N

WIN_PAR

N

N

Y

Y

N

Y

Y

Y

N

WIN_ORD

N

N

Y

Y

N

Y

Y

Y

N

SEL_EXP

N

N

Y

Y

N

Y

Y

Y

N

SEL_RAND

N

N

Y

Y

N

Y

Y

Y

N

SEL_RANDROW

N

N

Y

Y

N

Y

Y

Y

N

SEL_SUBQ

N

N

Y

Y

N

Y

Y

Y

N

SEL_WINDOW

N

N

Y

Y

N

Y

Y

Y

N

SEL_CNDRV

N

N

Y

Y

N

Y

Y

Y

N

SEL_NCNDRV

N

N

Y

Y

N

Y

Y

Y

N

JOIN

N

N

N

N

N

Y

Y

Y

N

IN_J_TBL

N

N

N

N

N

Y

N

N

N

J_TBL#12

N

N

N

N

N

Y

N

Y

N

FJ_TBL

N

N

N

N

N

N

N

N

N

TBLF_TBL

N

N

N

N

N

N

N

N

N

ANY_ARRAY_ELMREF

N

N

Y#13

Y#13

N

Y#14

Y

Y

N

RVC

N

N

D

D

N

Y

Y

Y

N

S_KEY

N

N

Y

Y

N

Y

Y

Y

N

SEXP_KEY

N

N

Y

Y

N

Y

Y

Y

N

O_REF

N

N

Y#15

D

N

Y

Y

Y

N

O_REF_FUNC

N

N

N

N

N

Y

Y

Y

N

Other items

N

N

Y

Y

N

Y

Y

Y

N
Table 7‒55: Summary of when derived table expansion is performed (3/3)

Specification of the SQL statement that manipulates a derived query

Specification of the derived query

U_ALL#3, #10

SET_OP#3, #10

SETOP_

VCH32000

Other items

SEL_DIST

Y

Y#1

N

Y

GRP

N

N

N

Y

GRP_EXP

N

N

N

Y

A-FUNC

N

N

N

Y

D-FUNC

N

N

N

Y

I-FUNC2

N

N

N

Y

WIN_AGG

N

N

N

Y

WIN_PAR

N

N

N

Y

WIN_ORD

N

N

N

Y

SEL_EXP

Y

Y

N

Y

SEL_RAND

Y

N

N

Y

SEL_RANDROW

Y

N

N

Y

SEL_SUBQ

Y

N

N

Y

SEL_WINDOW

Y

N

N

Y

SEL_CNDRV

Y

N

N

Y

SEL_NCNDRV

Y

N

N

Y

JOIN

N

N

N

Y

IN_J_TBL

N

N

N

Y

J_TBL#12

N

N

N

Y

FJ_TBL

N

N

N

N

TBLF_TBL

N

N

N

N

ANY_ARRAY_ELMREF

Y

Y

N

Y

RVC

Y

Y

N

Y

S_KEY

Y

Y

N

Y

SEXP_KEY

N

N

N

Y

O_REF

Y

Y

N

Y

O_REF_FUNC

N

N

N

Y

Other items

Y

Y

N

Y
Legend:

Y: Derived table expansion is performed.

D: In general, derived table expansion is performed. However, if you specify a derived column that is derived from the specification of the derived query in a location in which an SQL statement that manipulates a derived query is specified, derived table expansion is not performed.

N: Derived table expansion is not performed.

  • A-FUNC: The ALL set function, but only if a column of the derived query name is specified as an aggregated argument

  • ANY_ARRAY_ELMREF: An array element reference with ANY is specified

  • CJOIN: A comma join

  • D-FUNC: A DISTINCT set function, LISTAGG set function, or ARRAY_AGG set function, but only if a column of the derived query name is specified as an aggregated argument. Alternatively, only one inverse distribution function in which a column of the derived query name is specified as an aggregated argument.

  • I-FUNC2: Two or more inverse distribution functions in which a column of the derived query name is specified as an aggregated argument

  • FJ_TBL: A derived query name is specified in a table reference of FULL OUTER JOIN

  • TBLF_TBL: A derived query name is specified in a table reference that is referenced from the ADB_GENERATE_SERIES function specified in the table function derived table

  • FUNC_COL: A set function, but only if column specifications appear as aggregated arguments

  • FUNC_EXP: A set function, but only if no column specifications appear as aggregated arguments

  • GRP: GROUP BY clause, a HAVING clause, or a set function

  • GRP_EXP: GROUP BY clause without column specifications (such as scalar operations)

  • IN_J_TBL: The corresponding derived query name for the table reference of a joined table that is on the side filled with null values

  • JOIN: Multiple tables

  • J_TBL: A derived query name specified in a table reference to a joined table

  • LIMIT: A LIMIT clause

  • O_REF: A column of the derived query name used as an external reference column

  • O_REF_FUNC: A column of the derived query name used as an external reference column in the argument of a set function

  • RVC: The row value constructor is specified inside the predicate

  • S_KEY: A column of the derived query name specified in the selection expression used as a sort key

  • SEL_CNDRV: A derived column derived from a value expression that includes a column specification is not specified as a single column specification in a selection expression.

  • SEL_IFN: An inverse distribution function specified in a selection expression

  • SEL_DIST: A SELECT DISTINCT clause

  • SEL_EXP: Non-column specifications (such as scalar operations) used in a selection expression (even if column specifications are used in value expressions)

  • SEL_NCNDRV: A derived column derived from a value expression that does not include a column specification is not specified as a single column specification in a selection expression.

  • SEL_NCOL: A value expression consisting of non-column specifications in the selection expression

  • SEL_PRM: A dynamic parameter appears in the selection expression.

  • SEL_RAND: The selection expression includes the scalar function RANDOM or RANDOM_NORMAL.

  • SEL_RANDCRS: The selection expression includes the scalar function RANDOMCURSOR.

  • SEL_RANDROW: The selection expression includes the scalar function RANDOMROW.

  • SEL_SUBQ: A selection expression includes a scalar subquery.

  • SEL_WINDOW: A selection expression includes a window function.

  • SET_OP: Set operations specified in cases other than U_ALL

  • SETOP_VCH32000: Column of the VARCHAR or STRING type larger than 32,000 bytes among the columns derived by the result of a set operation

  • SEXP_KEY: A sort key value expression that is not specified as a selection expression

    Example:

    SELECT "C1"+"C2","C2" AS "DC1" FROM "T1" ORDER BY "C1"/"C2"

  • SUBQ: Subquery included in a selection expression

  • U_ALL: Set operations that specify only UNION ALL

  • WHERE_CE: If either of the following is specified in the WHERE clause

    • Row value constructors

    • Array element reference using ANY

  • WINDOW: Window function

  • WIN_AGG: Derived query name column specified in a set function, LAG function, or LEAD function specified as a window function

  • WIN_PAR: Derived query name column specified in a window partition clause in a window function

  • WIN_ORD: Derived query name column specified in a window order clause in a window function

#1

Expansion of the derived table is not performed if the set operator that is evaluated last in the set operations specified in the derived query is EXCEPT ALL.

Example where expansion is not performed:

SELECT DISTINCT "C1","C2" FROM (
                  (SELECT "C1","C2" FROM "T1"
                   UNION ALL
                   SELECT "C1","C2" FROM "T2")
                   EXCEPT ALL
                   SELECT "C1","C2" FROM "T2") AS "V1"

In the SQL statement above, expansion of the derived table is not performed because the set operator that is evaluated last is EXCEPT ALL.

Example where expansion is performed:

SELECT DISTINCT "C1","C2" FROM (
                    SELECT "C1","C2" FROM "T1"
                    UNION ALL
                   (SELECT "C1","C2" FROM "T2"
                    EXCEPT ALL
                    SELECT "C1","C2" FROM "T2")) AS "V1"

In the SQL statement above, expansion of the derived table is performed because the set operator that is evaluated last is UNION ALL.

#2

If all of the following conditions are met, derived table expansion is not performed:

  1. The derived query name column that was derived from a value expression is specified as a grouping column.

  2. The derived query name column in condition 1 is specified in either of the following items and is an external reference column:

  • Selection expression

    Example where expansion is not performed:

    SELECT (SELECT "C1" FROM "T2" WHERE "T2"."C1" = "V1"."DC1") "DC2"
    FROM (SELECT "C1"+1 AS "DC1" FROM "T1") AS "V1"
    GROUP BY "DC1"

  • HAVING clause

    Example where expansion is not performed:

    SELECT "DC1" FROM (SELECT "C1"+1 AS "DC1" FROM "T1") AS "V1"
    GROUP BY "DC1"
    HAVING EXISTS (SELECT * FROM "T2" WHERE "T2"."C1" = "V1"."DC1")
#3

If FULL OUTER JOIN is specified in a subquery that is included in a query specification for manipulating a derived table with a set operation specified, expansion of a set operation derived table is not performed.

Example where expansion is not performed:

SELECT * FROM (SELECT "C1","C2" FROM "T1"
               UNION ALL
               SELECT "C1","C2" FROM "T2") "DT2"
    WHERE "C1"=ANY(SELECT "X"."C1" FROM "T3" FULL OUTER JOIN "T4"
                                             ON "T3"."C2"> "T4"."C2")
#4

If one of the following value expressions is specified in the selection expression of the outermost query specification for a derived query, derived table expansion is not performed:

  • Scalar function COALESCE

  • Scalar function ISNULL

  • Scalar function NULLIF

  • Scalar function NVL

  • Scalar function DECODE

  • Scalar function LTDECODE

  • CASE expression

#5

If you specify, in the SQL statement, a derived column to which the details specified in the derived query apply, the derived table is not expanded. If you do not specify such a derived column, the derived table is expanded.

Example where expansion is not performed:

SELECT "DC1" FROM (SELECT RANDOM("C1","C2") AS "DC1" FROM "T1") AS "V1"

In the SQL statement above, the derived column "DC1" is specified in the SQL statement that manipulates the derived query. Because this derived column corresponds to a column in the selection expression that includes the scalar function RANDOM, which is specified in the derived query, the derived table is not expanded.

#6

If all of the following conditions are met, the derived table is expanded:

  • The number of grouping columns in the query specification for operating a derived query is the same as the number of grouping columns in the derived query.

  • All derived columns derived from the grouping column specified in a selection expression for a derived query are specified in a grouping column that has a query specification for operating the derived query.

Example where expansion is performed (1):

SELECT "C1","C2" 
    FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1"
    GROUP BY "C1","C2"

Example where expansion is performed (2):

SELECT "C1","DC2"
    FROM (SELECT "C1","C2"+1 AS "DC2" FROM "T1" GROUP BY "C1","C2"+1) AS "V1"
    GROUP BY "C1","DC2"
#7

If all of the following conditions are met, the derived table is expanded:

  • The GROUP BY clause or HAVING clause is specified in the query specification that operates the derived query.

  • All set functions included in the query specification that manipulates the derived query satisfy Condition 1 or Condition 2, as follows:

    Condition 1:

    • The set function included in the query specification that operates the derived query is COUNT(*)#.

    #

    The set function COUNT (with ALL specified) for which a literal (or a value expression equivalent to a literal) is specified as an argument is replaced by the set function COUNT(*) and is treated as the set function COUNT(*).

    Condition 2:

    • One of the following set functions is included in the query specification that operates the derived query:

      • Set function MAX with ALL specified

      • Set function MIN with ALL specified

      • Set function SUM with ALL specified

      • Set function AVG with ALL specified

    • A derived column consisting of the set functions specified in a derived query is specified as an aggregated argument of the preceding set function.

    Example where expansion is performed:

    SELECT "C1", "C2",SUM("C3")
    FROM (SELECT "C1","C2",COUNT("C3") AS "C3"
             FROM "T1"
             GROUP BY "C1","C2") "V1"
    GROUP BY "C1","C2"
#8

If a value expression that includes the derived column subject to the specification of the derived query is specified in an item other than a selection expression and ORDER BY clause, the derived table is not expanded.

#9

If the derived column subject to the specification of the derived query is specified in the scalar function RANDOMROW, the derived table is not expanded.

#10

The derived table is not expanded if at least one of the conditions under which an internal derived table is not expanded is satisfied in the following case: the query specification in an operand of a set operation specified in a derived query is assumed to be a derived query.

#11

The derived table is not expanded if the following derived column is not specified as a single column specification in a selection expression: a derived column derived from a value expression that does not include column specifications and includes any of the following specifications:

  • Scalar function RANDOM

  • Scalar function RANDOM_NORMAL

  • Scalar function RANDOMROW

  • Set function

#12

The HADB server might convert INNER JOIN or CROSS JOIN to a comma join. Therefore, if INNER JOIN or CROSS JOIN specified in a derived query is converted to a comma join, it is assumed that a comma join is included in a derived query (CJOIN in the preceding table applies). Also, if a joined table disappears from a query specification in a case where INNER JOIN or CROSS JOIN specified in a query specification that manipulates a derived query is converted to a comma join, it is assumed that no joined table is specified (J_TBL in the preceding table no longer applies).

#13

Derived column derived from value expressions including the ARRAY_AGG set function in a derived query is not expanded in the derived table if the following is specified in the query specification that operates on the derived query.

  • Array value expression of the array element reference using ANY

#14

If the following is specified in the WHERE clause of the outermost query specification manipulating a derived query, derived table expansion is not performed:

  • Predicate with the following specification

    • Array element reference with ANY specifying the column derived from the value expression containing the table column specification specified in the outermost query specification of the derived query

    • Column derived from a value expression including a column specification from a different table than the above specified in the outermost query specification of the derived query

  • Array element reference with ANY with the following same identification number

    • Array element reference specifying the column derived from the value expression containing the table column specification specified in the outermost query specification of the derived query

    • Array element reference specifying the column derived from a value expression including a column specification from a different table than the above specified in the outermost query specification of the derived query

#15

If a derived column derived from a value expression containing a LISTAGG set function or ARRAY_AGG set function in a derived query is specified as the external reference column, derived table expansion is not performed.

#16

If a derived column derived from a value expression including an array element reference in a derived query is specified in a row value constructor element of a row value constructor, derived table expansion is not performed.

#17

If a derived column derived from a value expression including an array-type column specification in a derived query (except for an array-type column specification specified alone and the scalar function ARRAY_MAX_CARDINALITY) is specified as the external reference column, derived table expansion is not performed.

#18

If a derived column derived from a value expression containing a column specification of type STRUCT in a derived query is specified as an external reference column, derived table expansion is not performed.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.