FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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