table-reference

A table-reference specifies an intermediate result table.

Read syntax diagramSkip visual syntax diagram
>>-+-single-table----------------+-----------------------------><
   +-nested-table-expression-----+   
   +-table-function--------------+   
   +-data-change-table-reference-+   
   +-collection-derived-table----+   
   '-joined-table----------------'   

single-table

|--+-table-name-+--+--------------------+-----------------------|
   '-view-name--'  '-correlation-clause-'   

nested-table-expression

|--+---------+--(--fullselect--)--correlation-clause------------|
   '-LATERAL-'                                         

table-function

|--TABLE--(--function-invocation--)--correlation-clause---------|

data-change-table-reference

|----+-FINAL-+--TABLE--(--INSERT statement--)--+--------------------+----|
     '-NEW---'                                 '-correlation-clause-'     

collection-derived-table

              .-,-----------------------------------------------.      
              V                                                 |      
|--UNNEST--(----+-array-variable-name-------------------------+-+--)-->
                '-CAST--(--parameter-marker--AS--data-type--)-'        

>--+-----------------+--correlation-clause----------------------|
   '-WITH ORDINALITY-'                       

correlation-clause

   .-AS-.                                                
|--+----+--correlation-name--+-----------------------+----------|
                             |    .-,-----------.    |   
                             |    V             |    |   
                             '-(----column-name-+--)-'   

Start of changeIf table-reference identifies a distributed table or a table that has a read trigger, the query cannot contain:End of change

The list of names in the FROM clause must conform to these rules:

Each correlation-name is defined as a designator of the intermediate result table specified by the immediately preceding table-reference. A correlation-name must be specified for nested table expressions and table functions.

The exposed names of all table references should be unique. An exposed name is:

Start of changeAny qualified reference to a column for a table, view, nested table expression, table function, collection-derived-table, or data-change-table-reference must use the exposed name. If the same table name or view name is specified twice, at least one specification should be followed by a correlation-name. The correlation-name is used to qualify references to the columns of the table or view. When a correlation-name is specified, column-names can also be specified to give names to the columns of the table-name, view-name, nested-table-expression, table-function, data-change-table-reference, or collection-derived-table. If a column list is specified, there must be a name in the column list for each column in the table or view and for each result column in the nested-table-expression, table-function, data-change-table-reference, or collection-derived-table. For more information, see Correlation names.End of change

Start of changeIn general, nested-table-expressions, table-functions, and collection-derived-tables can be specified in any FROM clause. Columns from the nested table expressions, table functions, and collection derived tables can be referenced in the select list and in the rest of the subselect using the correlation name which must be specified. The scope of this correlation name is the same as correlation names for other table or view names in the FROM clause. End of change

A nested table expression can be used:

Data change table references

A data-change-table-reference specifies an intermediate result table that is based on the rows that are directly changed by the INSERT statement included in the clause. A data-change-table-reference must be the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement, a SELECT INTO statement, a SET variable statement, or as the only fullselect in an assignment statement.

The intermediate result table for a data-change-table-reference includes all rows that were inserted. All columns of the inserted table may be referenced in the subselect, along with any INCLUDE columns defined on the INSERT statement. A data-change-table-reference has the following restrictions:

FINAL TABLE
Specifies that the rows of the intermediate result table represent the set of rows that are inserted by the SQL data change statement as they appear at the completion of the data change statement. If there are AFTER INSERT triggers or referential constraints that result in further changes to the inserted rows of the table that is the target of the data change statement, an error is returned.
NEW TABLE
Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement prior to the application of referential constraints and AFTER triggers. Data in the target table at the completion of the statement might not match the data in the intermediate result table because of additional processing for referential constraints and AFTER triggers.

Collection derived table

Start of changeA collection derived table can be used to unnest the elements of arrays into rows.End of change

Start of changeIf more than one array is specified, the first array provides the first column in the result table, the second array provides the second column, and so on. If WITH ORDINALITY is specified, an extra column of type BIGINT, which contains the position of the elements in the arrays, is appended. If the cardinalities of the arrays are not identical, the cardinality of the result table is the same as the array with the largest cardinality. The column values in the table are set to the null value for all rows whose subindex value is greater than the cardinality of the corresponding array. In other words, if each array is viewed as a table with two columns (one for the subindices and one for the data), then UNNEST performs an OUTER JOIN among the arrays using equality on the subindices as the join predicate.End of change

Start of changeUNNEST can only be specified within an SQL procedure.End of change

Correlated references in table-references

Correlated references can be used in nested-table-expressions. The basic rule that applies is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries. This hierarchy includes the table-references that have already been resolved in the left-to-right processing of the FROM clause. For nested table expressions, the TABLE or LATERAL keyword must appear before the fullselect. For more information see References to SQL parameters and SQL variables

A table function can contain one or more correlated references to other tables in the same FROM clause if the referenced tables precede the reference in the left-to-right order of the tables in the FROM clause. The same capability exists for nested table expressions if the optional keyword TABLE or LATERAL is specified. Otherwise, only references to higher levels in the hierarchy of subqueries is allowed.

A nested table expression or table function that contains correlated references to other tables in the same FROM clause:

If table-reference identifies a distributed table Start of changeorEnd of change a table that has a read trigger; a nested table expression cannot contain a correlated reference to other tables in the same FROM clause when:

Start of changeSyntax Alternatives: TABLE can be specified in place of LATERAL.End of change

Example 1

The following example is valid:

SELECT D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
FROM DEPARTMENT D,
  (SELECT AVG(E.SALARY) AS AVGSAL,COUNT (*) AS EMPCOUNT
     FROM EMPLOYEE E
     WHERE E.WORKDEPT =
       (SELECT X.DEPTNO
         FROM DEPARTMENT X
         WHERE X.DEPTNO = E.WORKDEPT ) ) AS EMPINFO   

The following example is not valid because the reference to D.DEPTNO in the WHERE clause of the nested-table-expression attempts to reference a table that is outside the hierarchy of subqueries:

SELECT D.DEPTNO, D.DEPTNAME,
        EMPINFO.AVGSAL, EMPINFO.EMPCOUNT                      ***INCORRECT***
  FROM DEPARTMENT D,
  (SELECT AVG(E.SALARY) AS AVGSAL,COUNT (*) AS EMPCOUNT
     FROM EMPLOYEE E
     WHERE E.WORKDEPT = D.DEPTNO ) AS EMPINFO

The following example is valid because the reference to D.DEPTNO in the WHERE clause of the nested-table-expression references DEPT, which precedes the nested-table-expression and the LATERAL keyword was specified:

SELECT D.DEPTNO, D.DEPTNAME,
        EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
  FROM DEPARTMENT D,
  LATERAL (SELECT AVG(E.SALARY) AS AVGSAL,COUNT (*) AS EMPCOUNT
     FROM EMPLOYEE E
     WHERE E.WORKDEPT = D.DEPTNO ) AS EMPINFO

Example 2

The following example of a table function is valid:

SELECT t.c1, z.c5
  FROM t, TABLE(tf3 (t.c2 ) ) ASWHERE t.c3 = z.c4

The following example is not valid because the reference to t.c2 is for a table that is to the right of the table function in the FROM clause:

SELECT t.c1, z.c5
  FROM TABLE(tf6 (t.c2 ) ) AS z, t                  ***INCORRECT***
  WHERE t.c3 = z.c4

Example 3

The following example of a table function is valid:

SELECT t.c1, z.c5
  FROM t, TABLE(tf4 (2 * t.c2 ) ) ASWHERE t.c3 = z.c4

The following example is not valid because the reference to b.c2 is for the table function that is to the right of the table function containing the reference to b.c2 in the FROM clause:

SELECT a.c1, b.c5
  FROM TABLE(tf7a (b.c2 ) ) AS z,                  ***INCORRECT***
       TABLE(tf7b (a.c6 ) ) AS b
  WHERE a.c3 = b.c4
1 A nested table expression is also called a derived table.