Table Expressions (XPS)

The term table expression refers to the use of a view name, a table name, or uncorrelated subquery in the FROM clause. These 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.

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.

Table expressions have the same syntax as general SELECT statements, but with the same restrictions that apply to subqueries in other contexts. A table expression cannot include the following syntax elements:

• ORDER BY clause
• SELECT INTO clause

In addition, table expressions are not valid in the following contexts:

• CREATE TRIGGER statements
• CREATE GK INDEX statements

Queries and correlated subqueries are not supported 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.

Usability and Performance Considerations

Although equivalent functionality is available through views, subqueries as table expressions simplify the formulation of queries, make the syntax more flexible and intuitive, and support the ANSI/ISO standard for SQL.

Performance might be affected, however, if you use table expressions. It is advisable to use subqueries if you really do not need to use table expressions.

The following are examples of valid table expressions:

SELECT * FROM (SELECT * FROM t);

SELECT * FROM (SELECT * FROM t) AS s;

SELECT * FROM (SELECT * FROM t) AS s WHERE t.a = s.b;

SELECT * FROM (SELECT * FROM t) AS s, (SELECT * FROM u) AS v WHERE s.a = v.b;

SELECT * FROM (SELECT * FROM t WHERE t.a = 1) AS s, 
OUTER
(SELECT * FROM u WHERE u.b = 2 GROUP BY 1) AS v WHERE s.a = v.b;

SELECT * FROM (SELECT a AS colA FROM t WHERE t.a = 1) AS s, 
OUTER
(SELECT b AS colB FROM u WHERE u.b = 2 GROUP BY 1) AS v
   WHERE s.colA = v.colB;

CREATE VIEW vu AS SELECT * FROM (SELECT * FROM t);

SELECT * FROM ((SELECT * FROM t) AS r) AS s;