FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Selecting FROM a derived table

A derived table is a valid SELECT statement enclosed in parentheses, optionally followed by a table alias and/or column aliases.The result set of the statement acts as a virtual table which the enclosing statement can query.

Syntax
(<query-expression>) [<correlation-or-recognition>]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]
Note

The SQL standard requires the <correlation-or-recognition>, and not providing one makes it hard to reference the derived table or its columns.For maximum compatibility and portability, we recommend always specifying an alias (correlation-name).

The result set returned by this “SELECT …​ FROM (SELECT FROM …​)” style of statement is a virtual table that can be queried within the enclosing statement, as if it were a regular table or view.

LATERAL Derived Tables

The keyword LATERAL marks a table as a lateral derived table.Lateral derived tables can reference tables (including other derived tables) that occur earlier in the FROM clause.See [fblangref50-dml-select-joins-lateral] for more information.