FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 
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.

Example using a derived table

The derived table in the query below returns the list of table names in the database, and the number of columns in each table.A “drill-down” query on the derived table returns the counts of fields and the counts of tables having each field count:

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
        ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
        GROUP BY RELATION)
GROUP BY FIELDCOUNT

A trivial example demonstrating how the alias of a derived table and the list of column aliases (both optional) can be used:

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO
        (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)
Note
More about Derived Tables

Derived tables can

  • be nested

  • be unions, and can be used in unions

  • contain aggregate functions, subqueries and joins

  • be used in aggregate functions, subqueries and joins

  • be calls to selectable stored procedures or queries to them

  • have WHERE, ORDER BY and GROUP BY clauses, FIRST/SKIP or ROWS directives, et al.

Furthermore,

  • Each column in a derived table must have a name.If it does not have a name, such as when it is a constant or a run-time expression, it should be given an alias, either in the regular way or by including it in the list of column aliases in the derived table’s specification.

    • The list of column aliases is optional but, if it exists, it must contain an alias for every column in the derived table

  • The optimizer can process derived tables very effectively.However, if a derived table is included in an inner join and contains a subquery, the optimizer will be unable to use any other join order.
A more useful example

Suppose we have a table COEFFS which contains the coefficients of a number of quadratic equations we have to solve.It has been defined like this:

create table coeffs (
  a double precision not null,
  b double precision not null,
  c double precision not null,
  constraint chk_a_not_zero check (a <> 0)
)

Depending on the values of a, b and c, each equation may have zero, one or two solutions.It is possible to find these solutions with a single-level query on table COEFFS, but the code will look messy and several values (like the discriminant) will have to be calculated multiple times per row.A derived table can help keep things clean here:

select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

If we want to show the coefficients next to the solutions (which may not be a bad idea), we can alter the query like this:

select
  a, b, c,
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select a, b, c, b*b - 4*a*c as D, 2*a as denom
     from coeffs)

Notice that whereas the first query used a column aliases list for the derived table, the second adds aliases internally where needed.Both methods work, as long as every column is guaranteed to have a name.

Important

All columns in the derived table will be evaluated as many times as they are specified in the main query.This is important, as it can lead to unexpected results when using non-deterministic functions.The following shows an example of this.

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

The result if this query produces three different values:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3  EB176C10-F754-4689-8B84-64B666381154

To ensure a single result of the GEN_UUID function, you can use the following method:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1 = 0) T;

This query produces a single result for all three columns:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

An alternative solution is to wrap the GEN_UUID query in a subquery:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT
        (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

This is an artifact of the current implementation.This behaviour may change in a future Firebird version.