Table expressions

A table expression (sometimes called a derived table) is the name of a table or view, or a specification that evaluates to a set of rows. These rows are typically the result of a query that is embedded in a nested SELECT statement, or in some other SQL statement.

Table expressions can have the following syntax. These options are a subset of the table options in the ANSI Table Reference and the Other Table Reference in the FROM Clause.

Figure 1: Table expression

Notes:

¹ HCL OneDB™ extension
² See Collection-Derived Table
³ See Collection Subquery
⁴ See Lateral derived tables
⁵ See Iterator Functions


Element	Description	Restrictions	Syntax
`subquery`	Nested query whose results are available to the outer query	See Usage notes below	SELECT statement
`table _object`	Name, synonym, or alias of a table, view, or EXTERNAL table	Must exist, or must reference a derived table that the SELECT statement creates	Identifier or Database Object Name

Usage

Table expressions can be simple or complex:

Simple table expressions
A simple table expression is one whose underlying query can be folded into the main query while preserving the correctness of the query result.
Complex table expressions
A complex table expression is one whose underlying query cannot be folded into the main query while preserving the correctness of the query result. The database server materializes such table expressions into a temporary table that is used in the main query. Subqueries in the FROM clause that specify aggregates, set operators, or the ORDER BY clause are implemented as complex table expressions, which typically require more resources of the database server than simple table expressions.

In either case, the table expression is evaluated as a general SQL query and its results can be thought of as a logical table. This logical table and its columns can be used just like an ordinary base table, but it is not persistent. It exists only during the execution of the query that references it.

Restrictions on table expressions

Table expressions have the same syntax as general SELECT statements, but with most of the restrictions that apply to subqueries in other contexts. A table expression cannot include the SELECT INTO clause that explicitly creates a result table.

HCL OneDB does not support Generalized Key indexes. It supports table expressions in the triggered actions of CREATE TRIGGER statements, and as the triggering event of a Select trigger. HCL OneDB also supports the ORDER BY clause in table expressions.

HCL OneDB supports iterator functions as FROM clause table expressions. The CALL statement of SPL, however, cannot invoke an iterator TABLE function within a subquery in the FROM clause.

Apart from these restrictions, any valid SQL query can be a table expression. A table expression can be nested within another table expression and can include tables and views in its definition. You can use table expressions in CREATE VIEW statements to define views.

Correlated subqueries and derived tables

A correlated subquery is a subquery that refers to a column of a table that is not listed in its FROM clause. Conversely, any subquery that references only columns in tables that are listed in its FROM clause is an uncorrelated subquery.

In the following example, the uncorrelated subquery that defines a derived table in its FROM clause contains a correlated subquery in its WHERE clause:

SELECT * FROM (SELECT * FROM t1 
   WHERE a IN (SELECT b FROM t2 WHERE t1.a = t2.b));

Here the subquery in the first WHERE clause is a correlated subquery, because it references column a of table t1, but its FROM clause specifies only table t2.

In FROM clause table expressions, HCL OneDB also supports the ORDER BY clause, which is not valid in subqueries outside the FROM clause. Columns or expressions that are specified by the ORDER BY clause in a table expression need not be included in the Projection clause.