TABLE Expression

A TableExpression specifies a table, view, or function in a  FROM clause. It is the source from which a TableExpression selects a result.

Syntax

{
   JOIN operations
}

Usage

A correlation name can be applied to a table in a TableExpression so that its columns can be qualified with that name.

  • If you do not supply a correlation name, the table name qualifies the column name.
  • When you give a table a correlation name, you cannot use the table name to qualify columns.
  • You must use the correlation name when qualifying column names.
  • No two items in the FROM clause can have the same correlation name, and no correlation name can be the same as an unqualified table name specified in that FROM clause.

In addition, you can give the columns of the table new names in the AS clause. Some situations in which this is useful:

  • When a TableSubquery, since there is no other way to name the columns of a VALUES expression.
  • When column names would otherwise be the same as those of columns in other tables; renaming them means you don’t have to qualify them.

The Query in a TableSubquery.

Example

   -- SELECT from a JOIN expression
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
  FROM EMPLOYEE E LEFT OUTER JOIN
       DEPARTMENT INNER JOIN EMPLOYEE M
       ON MGRNO = M.EMPNO
       ON E.WORKDEPT = DEPTNO;

TableViewOrFunctionExpression

{
   { view-Name }
   [ CorrelationClause ]  |
   { TableSubquery | TableFunctionInvocation }
   CorrelationClause
}

where CorrelationClause is

    [ AS ]
    correlation-Name
    [ ( Simple-column-Name * ) ]

TableFunctionExpression

{
  TABLE function-name( [ [ function-arg ] [, function-arg ]* ] )
}

Note that when you invoke a table function, you must bind it to a correlation name. For example:

splice> SELECT s.* FROM TABLE( externalEmployees( 42 ) ) s;

See Also