A table-reference specifies an intermediate result table.
>>-+-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-+--)-'
If a collection-derived-table is specified,
the intermediate result table is a set of rows from one or more arrays.
If table-reference identifies a distributed
table or a table that has a read trigger, the query cannot contain:
ORDER BY or FETCH FIRST n ROWS clause in a subselect,
CONTAINS or SCORE functions,
XMLAGG, XMLATTRIBUTES, XMLCOMMENT, XMLCONCAT, XMLDOCUMENT,
XMLELEMENT, XMLFOREST, XMLGROUP, XMLNAMESPACES, XMLPI, XMLROW, or
XMLTEXT functions, 
global variables, or
references to arrays.
The list of names in the FROM clause must conform to these rules:
Each array-variable-name must identify an array
variable in the SQL procedure.
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:
Any 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.
In 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. 
A nested table expression can be used:
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:
A collection
derived table can be used to unnest the elements of arrays into rows.
If 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.
UNNEST can only be specified within an SQL procedure.
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
or
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:
Syntax Alternatives: TABLE can be
specified in place of LATERAL.
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
The following example of a table function is valid:
SELECT t.c1, z.c5 FROM t, TABLE(tf3 (t.c2 ) ) AS z WHERE 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
The following example of a table function is valid:
SELECT t.c1, z.c5 FROM t, TABLE(tf4 (2 * t.c2 ) ) AS z WHERE 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