SQL Language Extension: derived tables

Function:
    A derived table is a table derived from a select statement.
    Derived tables can also be nested to build complex queries.
    They can be joined the same as normal tables and views.

Author:
    Arno Brinkman <firebird@abvisie.nl>

Format:
    SELECT
      <select list>
    FROM
      <table reference list>

    <table reference list> ::= <table reference> [{<comma> <table reference>}...]

    <table reference> ::= 
        <table primary>
      | <joined table>

    <table primary> ::=
        <table> [[AS] <correlation name>]
      | <derived table>

    <derived table> ::=
        <query expression> [[AS] <correlation name>] 
          [<left paren> <derived column list> <right paren>]

    <derived column list> ::= <column name> [{<comma> <column name>}...]

Notes:
    Every column in the derived table must have a name. Unnamed expressions like 
    constants should be added with an alias or the column list should be used.
    The number of columns in the column list should be the same as the number of 
    columns from the query expression.
    The optimizer can handle a derived table very efficiently, but if the 
    derived table contains a sub-select then no join order can be made (if the 
    derived table is included in an inner join).

Examples:

a) Simple derived table:

  SELECT
    *
  FROM
    (SELECT
       RDB$RELATION_NAME, RDB$RELATION_ID
     FROM
       RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)


b) Aggregate on a derived table which also contains an aggregate

  SELECT
    DT.FIELDS,
    Count(*)
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       Count(*)
     FROM
       RDB$RELATIONS R
       JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
     GROUP BY
       R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
  GROUP BY
    DT.FIELDS


c) UNION and ORDER BY example:

  SELECT
    DT.*
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     UNION ALL
     SELECT
       R.RDB$OWNER_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     ORDER BY
       2) AS DT
  WHERE
    DT.RDB$RELATION_ID <= 4