FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Selecting FROM a Common Table Expression (CTE)

A common table expression — or CTE — is a more complex variant of the derived table, but it is also more powerful.A preamble, starting with the keyword WITH, defines one or more named CTEs, each with an optional column aliases list.The main query, which follows the preamble, can then access these CTEs as if they were regular tables or views.The CTEs go out of scope once the main query has run to completion.

For a full discussion of CTEs, please refer to the section [fblangref50-dml-select-cte].

The following is a rewrite of our derived table example as a CTE:

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

Except for the fact that the calculations that have to be made first are now at the beginning, this isn’t a great improvement over the derived table version.However, we can now also eliminate the double calculation of sqrt(D) for every row:

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

The code is a little more complicated now, but it might execute more efficiently (depending on what takes more time: executing the SQRT function or passing the values of b, D and denom through an extra CTE).Incidentally, we could have done the same with derived tables, but that would involve nesting.

Important

All columns in the CTE 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.

WITH T (X) AS (
  SELECT GEN_UUID()
  FROM RDB$DATABASE)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM 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:

WITH T (X) AS (
  SELECT GEN_UUID()
  FROM RDB$DATABASE
  UNION ALL
  SELECT NULL FROM RDB$DATABASE WHERE 1 = 0)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM 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:

WITH T (X) AS (
  SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
  FROM RDB$DATABASE)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM T;

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

Joins

Joins combine data from two sources into a single set.This is done on a row-by-row basis and usually involves checking a join condition to determine which rows should be merged and appear in the resulting dataset.There are several types (INNER, OUTER) and classes (qualified, natural, etc.) of joins, each with its own syntax and rules.

Since joins can be chained, the datasets involved in a join may themselves be joined sets.

Syntax
SELECT
   ...
   FROM <table-reference> [, <table-reference> ...]
   [...]

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

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

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

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

<derived-table> ::= (<query-expression>)

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }
Table 1. Arguments for JOIN Clauses
Argument Description

table-name

Name of a table or view

query-name

Name of a CTE

package-name

Name of a package

procedure-name

Name of a selectable stored procedure

procedure-args

Selectable stored procedure input parameter(s)

derived-table

Derived table query expression

correlation-name

The alias of a data source (table, view, procedure, CTE, derived table)

column-name

Name or alias for a column in a relation, CTE or derived table

search-condition

Join condition (criterion)

column-name-list

List of aliases of the columns of a derived table, or the list of columns used for an equi-join

Inner vs. Outer Joins

A join combines data rows from two sets (usually referred to as the left set and the right set).By default, only rows that meet the join condition (i.e. that match at least one row in the other set when the join condition is applied) make it into the result set.This default type of join is called an inner join.Suppose we have the following two tables:

Table A
ID S

87

Just some text

235

Silence

Table B
CODE X

-23

56.7735

87

416.0

If we join these tables like this:

select *
  from A
  join B on A.id = B.code;

then the result set will be:

ID S CODE X

87

Just some text

87

416.0

The first row of A has been joined with the second row of B because together they met the condition “A.id = B.code”.The other rows from the source tables have no match in the opposite set and are therefore not included in the join.Remember, this is an INNER join.We can make that fact explicit by writing:

select *
  from A
  inner join B on A.id = B.code;

However, since INNER is the default, it is usually omitted.

It is perfectly possible that a row in the left set matches several rows from the right set or vice versa.In that case, all those combinations are included, and we can get results like:

ID S CODE X

87

Just some text

87

416.0

87

Just some text

87

-1.0

-23

Don’t know

-23

56.7735

-23

Still don’t know

-23

56.7735

-23

I give up

-23

56.7735

Sometimes we want (or need) all the rows of one or both of the sources to appear in the joined set, even if they don’t match a record in the other source.This is where outer joins come in.A LEFT outer join includes all the records from the left set, but only matching records from the right set.In a RIGHT outer join it’s the other way around.A FULL outer joins include all the records from both sets.In all outer joins, the “holes” (the places where an included source record doesn’t have a match in the other set) are filled up with NULLs.

To make an outer join, you must specify LEFT, RIGHT or FULL, optionally followed by the keyword OUTER.

Below are the results of the various outer joins when applied to our original tables A and B:

select *
  from A
  left outer join B on A.id = B.code;
ID S CODE X

87

Just some text

87

416.0

235

Silence

<null>

<null>

select *
  from A
  right outer join B on A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

select *
  from A
  full outer join B on A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

235

Silence

<null>

<null>

Qualified joins

Qualified joins specify conditions for the combining of rows.This happens either explicitly in an ON clause or implicitly in a USING clause.

Syntax
<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]