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 expression
(explicit id tabx001) tabx001 (explicit id tabx002) tabx002 (explicit id tabx003) tabx003 (explicit id tabx004) tabx004 (explicit id tabx005) tabx005 { table_object | ( subquery ) | [] <Collection-Derived Table> [] | ( [] <Collection Subquery> ) [] | <Lateral derived table> [] | [] <Iterator> [] }| 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
- 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.
Informix® 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. Informix® also supports the ORDER BY clause in table expressions.
Informix® 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.
SELECT * FROM (SELECT * FROM t1
WHERE a IN (SELECT b FROM t2 WHERE t1.a = t2.b));In FROM clause table expressions, HCL Informix® 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.