Lateral derived tables

The LATERAL keyword must immediately precede any query in the FROM clause that defines a derived table, if that query references any other table or column that appears earlier in the same FROM clause than the query that defines the derived table.

Lateral derived tables, and the scope of reference of the table and column aliases that can be declared in their syntax, are part of the ISO/ANSI standard for the SQL language. This syntax fragment is part of the FROM Clause.


LATERAL derived table

|--LATERAL -(--subquery--)--+----+--alias--+------------------------+--|
                            '-AS-'         |    .-,------------.    |   
                                           |    V              |    |   
                                           '-(----column_alias-+--)-'

Element	Description	Restrictions	Syntax
`alias`	Temporary name declared here for the derived table of `subquery` results	See Aliases for Tables or Views.	Identifier
`column_alias`	Temporary name declared here for a column in the derived table		Identifier
`subquery`	Specifies rows to be retrieved	Can be uncorrelated or correlated	SELECT statement

Usage

The LATERAL keyword is required if the subquery whose result set is the derived table references any table or column that appears earlier in the same FROM clause. Here earlier means "to the left of the derived-table" in the left-to-right order of syntax tokens in the FROM clause. A derived table defined with the LATERAL keyword is called a lateral derived table.

This support for references to columns in other tables in the FROM clause, rather than only to columns in subsequent derived tables, can improve performance in SELECT statements that join one or more derived tables. Lateral table and column references are also valid in derived tables within DELETE, UPDATE, and CREATE VIEW statements.

The LATERAL keyword is not required in the FROM clause for derived tables in which all uncorrelated table and column references have already been resolved.

Restrictions on lateral correlated references

The following restrictions apply to lateral derived table and column references:

They cannot be used in ANSI FULL OUTER JOIN queries.
They cannot be used in ANSI RIGHT OUTER JOIN queries.
They cannot be used in Informix-extension OUTER JOIN queries.

Examples of lateral derived tables

The following query includes a lateral derived table in the FROM clause, where t1_a is a lateral correlation reference:

SELECT * FROM t1 , 
            LATERAL (SELECT t2.a AS t2_a 
                     FROM t2 WHERE t2.a = t1.a);

In the next example, d.deptno is a lateral correlation reference:

   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;

Here the avgsal and empcount aliases for column expressions and the empinfo lateral table reference appear in the projection list of the outer query, which joins qualifying rows from the department table and the derived table, using the correlation deptno.

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.

The query optimizer does not materialize simple table expressions that the FROM clause specifies. The performance of a query that uses the ANSI/ISO syntax for a table expression in the FROM clause is at least as good as a that of a query that uses the Informix-extension TABLE (MULTISET ( SELECT ... )) syntax to specify an equivalent derived table in the FROM clause. Subqueries in the FROM clause that include aggregate functions, set operators like the UNION, INTERSECT, or MINUS operators, or ORDER BY specifications are implemented as complex table expressions that can impose greater costs than simple table expressions. Use the SET EXPLAIN statement to examine the query plan and the estimated cost of a table expression.

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 SKIP 2 col1 FROM tab1 WHERE col1 > 50 ORDER BY col1  DESC);

SELECT * FROM (SELECT col1,col3 FROM tab1 
   WHERE col1 < 50 GROUP BY col1,col3 ORDER BY col3 ) vtab(vcol0,vcol1);

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;