FirebirdSQL logo

Recursive CTEs

A recursive (self-referencing) CTE is a UNION which must have at least one non-recursive member, called the anchor.The non-recursive member(s) must be placed before the recursive member(s).Recursive members are linked to each other and to their non-recursive neighbour by UNION ALL operators.The unions between non-recursive members may be of any type.

Recursive CTEs require the RECURSIVE keyword to be present right after WITH.Each recursive union member may reference itself only once, and it must do so in a FROM clause.

A great benefit of recursive CTEs is that they use far less memory and CPU cycles than an equivalent recursive stored procedure.

Execution Pattern

The execution pattern of a recursive CTE is as follows:

  • The engine begins execution from a non-recursive member.

  • For each row evaluated, it starts executing each recursive member one by one, using the current values from the outer row as parameters.

  • If the currently executing instance of a recursive member produces no rows, execution loops back one level and gets the next row from the outer result set.

Example of recursive CTEs
WITH RECURSIVE DEPT_YEAR_BUDGET AS (
  SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
  FROM PROJ_DEPT_BUDGET
  GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS (
  SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
  FROM DEPARTMENT
  WHERE HEAD_DEPT IS NULL
  UNION ALL
  SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
  FROM DEPARTMENT D
    JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
)
SELECT
    D.DEPT_NO,
    D.INDENT || D.DEPARTMENT DEPARTMENT,
    DYB_2008.BUDGET AS BUDGET_08,
    DYB_2009.BUDGET AS BUDGET_09
FROM DEPT_TREE D
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
      (D.DEPT_NO = DYB_2008.DEPT_NO) AND
      (DYB_2008.FISCAL_YEAR = 2008)
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
      (D.DEPT_NO = DYB_2009.DEPT_NO) AND
      (DYB_2009.FISCAL_YEAR = 2009);

The next example returns the pedigree of a horse.The main difference is that recursion occurs simultaneously in two branches of the pedigree.

WITH RECURSIVE PEDIGREE (
  CODE_HORSE,
  CODE_FATHER,
  CODE_MOTHER,
  NAME,
  MARK,
  DEPTH)
AS (SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      CAST('' AS VARCHAR(80)),
      0
    FROM
      HORSE
    WHERE
      HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'F' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
      PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'M' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
      PEDIGREE.DEPTH < :MAX_DEPTH
)
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE
Notes on recursive CTEs
  • Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM, COUNT, MAX etc) are not allowed in recursive union members.

  • A recursive reference cannot participate in an outer join.

  • The maximum recursion depth is 1024.

Full SELECT Syntax

The previous sections used incomplete or simplified fragments of the SELECT syntax.Following is the full syntax.

Note

Where possible, the syntax below uses syntax names from the SQL standard, which do not necessarily match the syntax names in the Firebird source.In some cases, syntax productions have been collapsed, because the productions in the SQL standard are verbose as they are also used to add additional rules or definitions to a syntax element.

Although this is intended as the full syntax, some productions are not shown (e.g. <value-expression>) and assumed to be clear for the reader, and in some cases we take shortcuts like using query-name or column-alias for identifiers in a syntax production.

If you come across situations where these shortcuts do result in lack of clarity or other issues, let us know on https://github.com/FirebirdSQL/firebird-documentation or on firebird-devel.

The syntax below does not include the PSQL SELECT …​ INTO syntax, which is essentially <cursor-specification> INTO <variable-list>.

<cursor-specification> ::=
  <query-expression> [<updatability-clause>] [<lock-clause>]

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

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

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

<limit-clause> ::= [FIRST <limit-expression>] [SKIP <limit-expression>]

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<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>)]

<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>) }

<window-definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

<rows-clause> ::= ROWS <value-expression> [TO <value-expression>]

<result-offset-clause> :: =
  OFFSET <offset-fetch-expression> { ROW | ROWS }

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>

<fetch-first-clause> ::=
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<updatability-clause> ::= FOR UPDATE [OF <column-name-list>]

<lock-clause> ::= WITH LOCK [SKIP LOCKED]

The SELECT Columns List

The columns list contains one or more comma-separated value expressions.Each expression provides a value for one output column.Alternatively, * (“star” or “all”) can be used to stand for all the columns of all relations in the FROM clause.

Syntax
SELECT
  [...]
  [{ ALL | DISTINCT }] <select-list>
  [...]
  FROM ...

<select_list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<value-expression> ::=
    [table-alias.]col_name
  | [table-alias.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | any other expression returning a single
    value of a Firebird data type or NULL

<function-call> ::=
    <normal-function>
  | <aggregate-function>
  | <window-function>

<normal-function> ::=
  !! See Built-in Scalar Functions !!

<aggregate-function> ::=
  !! See Aggregate Functions !!

<window-function> ::=
  !! See Window Functions !!
Table 1. Arguments for the SELECT Columns List
Argument Description

table-alias

Name of relation (view, stored procedure, derived table), or its alias

col_name

Name of a table or view column, or its alias

selectable_SP_outparm

Declared name of an output parameter of a selectable stored procedure

literal

A literal

context-variable

Context variable

function-call

Scalar, aggregate, or window function expression

single-value-subselect

A subquery returning one scalar value (singleton)

CASE-construct

CASE construct setting conditions for a return value

It is always valid to qualify a column name (or “*”) with the name or alias of the table, view or selectable SP to which it belongs, followed by a dot (‘.’).For example, relationname.columnname, relationname.*, alias.columnname, alias.*.Qualifying is required if the column name occurs in more than one relation taking part in a join.Qualifying “*” is required if it is not the only item in the column list.

Important

Aliases hide the original relation name: once a table, view or procedure has been aliased, only the alias can be used as its qualifier throughout the query.The relation name itself becomes unavailable.

The column list may optionally be preceded by one of the keywords DISTINCT or ALL:

  • DISTINCT filters out any duplicate rows.That is, if two or more rows have the same values in every corresponding column, only one of them is included in the result set

  • ALL is the default: it returns all rows, including duplicates.ALL is rarely used;it is allowed for compliance with the SQL standard.

A COLLATE clause of a value-expression will not change the appearance of the column as such.However, if the specified collation changes the case or accent sensitivity of the column, it may influence:

  • The ordering, if an ORDER BY clause is also present, and it involves that column

  • Grouping, if the column is part of a GROUP BY clause

  • The rows retrieved (and hence the total number of rows in the result set), if DISTINCT is used

Examples of SELECT queries with different types of column lists

A simple SELECT using only column names:

select cust_id, cust_name, phone
  from customers
  where city = 'London'

A query featuring a concatenation expression and a function call in the columns list:

select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
  from contacts
  where date_last_purchase(id) = current_date

A query with two subselects:

select p.fullname,
  (select name from classes c where c.id = p.class) as class,
  (select name from mentors m where m.id = p.mentor) as mentor
from pupils p

The following query accomplishes the same as the previous one using joins instead of subselects:

select p.fullname,
  c.name as class,
  m.name as mentor
  join classes c on c.id = p.class
from pupils p
  join mentors m on m.id = p.mentor

This query uses a CASE construct to determine the correct title, e.g. when sending mail to a person:

select case upper(sex)
    when 'F' then 'Mrs.'
    when 'M' then 'Mr.'
    else ''
  end as title,
  lastname,
  address
from employees

Query using a window function, ranks employees by salary.

SELECT
  id,
  salary,
  name ,
  DENSE_RANK() OVER (ORDER BY salary) AS EMP_RANK
FROM employees
ORDER BY salary;

Querying a selectable stored procedure:

select * from interesting_transactions(2010, 3, 'S')
  order by amount

Selecting from columns of a derived table.A derived table is a parenthesized SELECT statement whose result set is used in an enclosing query as if it were a regular table or view.The derived table is shown in bold here:

select fieldcount,
  count(relation) as num_tables
from (select r.rdb$relation_name as 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

Asking the time through a context variable (CURRENT_TIME):

select current_time from rdb$database

For those not familiar with RDB$DATABASE: this is a system table that is present in all Firebird databases and is guaranteed to contain exactly one row.Although it wasn’t created for this purpose, it has become standard practice among Firebird programmers to select from this table if you want to select “from nothing”, i.e. if you need data that are not bound to a table or view, but can be derived from the expressions in the output columns alone.Another example is:

select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
  from rdb$database

Finally, an example where you select some meaningful information from RDB$DATABASE itself:

select rdb$character_set_name from rdb$database

As you may have guessed, this will give you the default character set of the database.