from-clause

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram         .-,---------------.
         V                 |
>>-FROM----table-reference-+-----------------------------------><
 

The FROM clause specifies an intermediate result table.

If only one table-reference is specified, the intermediate result table is simply the result of that table-reference. If more than one table-reference is specified in the FROM clause, the intermediate result table consists of all possible combinations of the rows of the specified table-references (the Cartesian product). Each row of the result is a row from the first table-reference concatenated with a row from the second table-reference, concatenated in turn with a row from the third, and so on. The number of rows in the result is the product of the number of rows in all the individual table-references.

table-reference

 

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-+-single-table------------+---------------------------------><
   +-nested-table-expression-+
   +-table-function----------+
   '-joined-table------------'
 
single-table:
 
|--+-table-name-+--+--------------------+-----------------------|
   '-view-name--'  '-correlation-clause-'
 
nested-table-expression:
 
|--+---------+--(--fullselect--+-----------------+--+--------------------+--)-->
   '-LATERAL-'                 '-order-by-clause-'  '-fetch-first-clause-'
 
>--correlation-clause-------------------------------------------|
 
table-function:
 
|--TABLE--(--function-invocation--)--correlation-clause---------|
 
correlation-clause:
 
   .-AS-.
|--+----+--correlation-name--+-----------------------+----------|
                             |    .-,-----------.    |
                             |    V             |    |
                             '-(----column-name-+--)-'
 

 

A table-reference specifies an intermediate result table.

If function-name is specified, the TABLE or LATERAL keyword is specified, or a table-reference identifies a distributed table, a table that has a read trigger, a DDS-created logical file, or logical file built over multiple physical file members; the query cannot contain:

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:

Any qualified reference to a column for a table, view, nested table expression, or table function 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 or table-function. 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 or table-function. For more information, see Correlation names.

In general, nested-table-expressions and table-functions can be specified in any FROM clause. Columns from the nested table expressions and table functions 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:

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 Column name qualifiers to avoid ambiguity

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:

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.

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

joined-table

 

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram                        .-INNER----------------.
|--+-table-reference--+-+----------------------+--JOIN--table-reference--+-ON--join-condition-----------+-+-+--|
   |                  | |            .-OUTER-. |                         |           .-,-----------.    | | |
   |                  | +-+-LEFT--+--+-------+-+                         |           V             |    | | |
   |                  | | '-RIGHT-'            |                         '-USING--(----column-name-+--)-' | |
   |                  | | .-LEFT--.            |                                                          | |
   |                  | '-+-------+--EXCEPTION-'                                                          | |
   |                  |   '-RIGHT-'                                                                       | |
   |                  '-CROSS JOIN--table-reference-------------------------------------------------------' |
   '-(--joined-table--)-------------------------------------------------------------------------------------'
 

 

A joined-table specifies an intermediate result table that is the result of either an inner, outer, cross, or exception join. The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, LEFT EXCEPTION, RIGHT EXCEPTION or CROSS to its operands.

If a join operator is not specified, INNER is implicit. The order in which multiple joins are performed can affect the result. Joins can be nested within other joins. The order of processing for joins is generally from left to right, but based on the position of the required join-condition or USING clause. Parentheses are recommended to make the order of nested joins more readable. For example:

   TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1
       LEFT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1
               ON TB1.C1=TB3.C1

is the same as

   (TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1)
        LEFT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1)
              ON TB1.C1=TB3.C1

An inner join combines each row of the left table with every row of the right table keeping only the rows where the join-condition (or USING clause) is true. Thus, the result table may be missing rows of from either or both of the joined tables. Outer joins include the rows produced by the inner join as well as the missing rows, depending on the type of outer join. Exception joins include only the missing rows, depending on the type of exception join as follows:

A joined table can be used in any context in which any form of the SELECT statement is used. A view or a cursor is read-only if its SELECT statement includes a joined table.

Join condition

The join-condition is a search-condition that must conform to these rules:

For any type of join, column references in an expression of the join-condition are resolved using the rules for resolution of column name qualifiers specified in Column names before any rules about which tables the columns must belong to are applied.

Join USING

The USING clause specifies a shorthand way of defining the join condition. This form is known as a named-columns-join.

column-name
Must unambiguously identify a column that exists in both table-references of the joined table. The column must not be a DATALINK column.

The USING clause is equivalent to a join-condition in which each column from the left table-reference is compared equal to a column of the same name in the right table-reference. For example, a named-columns-join of the form:

   TB1 INNER JOIN TB2
       USING (C1, C2, ... Cn)

is equivalent to:

   TB1 INNER JOIN TB2 
       ON TB1.C1 = TB2.C1 AND
          TB1.C2 = TB2.C2 AND
          ...
          TB1.Cn = TB2.Cn 
Join operations

A join-condition (or USING clause) specifies pairings of T1 and T2, where T1 and T2 are the left and right operand tables of the JOIN operator of the join-condition (or USING clause). For all possible combinations of rows of T1 and T2, a row of T1 is paired with a row of T2 if the join-condition (or USING clause) is true. When a row of T1 is joined with a row of T2, a row in the result consists of the values of that row of T1 concatenated with the values of that row of T2. In the case of OUTER joins, the execution might involve the generation of a null row. The null row of a table consists of a null value for each column of the table, regardless of whether the columns allow null values.

INNER JOIN or JOIN
The result of T1 INNER JOIN T2 consists of their paired rows.

Using the INNER JOIN syntax with a join-condition (or USING clause) will produce the same result as specifying the join by listing two tables in the FROM clause separated by commas and using the where-clause to provide the condition.

LEFT JOIN or LEFT OUTER JOIN
The result of T1 LEFT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values.
RIGHT JOIN or RIGHT OUTER JOIN
The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values.
LEFT EXCEPTION JOIN and EXCEPTION JOIN
The result of T1 LEFT EXCEPTION JOIN T2 consists only of each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values.
RIGHT EXCEPTION JOIN
The result of T1 RIGHT EXCEPTION JOIN T2 consists only of each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values.
CROSS JOIN
The result of T1 CROSS JOIN T2 consists of each row of T1 paired with each row of T2. CROSS JOIN is also known as cartesian product.

53.
A nested table expression is also called a derived table.