Lateral derived tables

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

Examples of lateral derived tables

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 HCL OneDB-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.

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;