FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

SELECT

“Queries” or retrieves data from the database

Global syntax
[WITH [RECURSIVE] <cte> [, <cte> ...]]
SELECT
  [FIRST <limit-expression>] [SKIP <limit-expression>]
  [{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>]
[UNION [{DISTINCT | ALL}] <query-term>]
[ORDER BY <sort-specification [, <sort-specification> ...]]
[{ ROWS <value-expression> [TO <value-expression>]
 | [OFFSET <offset-fetch-expression> {ROW | ROWS}]
   [FETCH {FIRST | NEXT}
    [<offset-fetch-expression>] {ROW | ROWS} ONLY]
}]
[FOR UPDATE [OF <column-name-list>]]
[WITH LOCK [SKIP LOCKED]]
[OPTIMIZE FOR {FIRST | ALL} ROWS]
[INTO <variable-list>]

<variable-list> ::= <variable> [, <variable> ...]

<variable> ::= varname | ':' varname
Note

The above syntax is not the full SELECT syntax.For documentation reasons it is simplified, and we attempt to build out the syntax in later sections.The full SELECT syntax can be found below, in [fblangref50-dml-select-full-syntax].

The SELECT statement retrieves data from the database and hands them to the application or the enclosing SQL statement.Data is returned in zero or more rows, each containing one or more columns or fields.The total of rows returned is the result set of the statement.

The only mandatory parts of the SELECT statement are:

  • The SELECT keyword, followed by a select list.This part specifies what you want to retrieve.

  • The FROM keyword, followed by a selectable object.This tells the engine where you want to get it from.

In its most basic form, SELECT retrieves a number of columns from a single table or view, like this:

select id, name, address
  from contacts

Or, to retrieve all the columns:

select * from sales

In practice, a SELECT statement is usually executed with a WHERE clause, which limits the rows returned.The result set may be sorted by an ORDER BY clause, and FIRST …​ SKIP, OFFSET …​ FETCH or ROWS may further limit the number of returned rows, and can — for example — be used for pagination.

The column list may contain all kinds of expressions, not only column names, and the source need not be a table or view: it may also be a derived table, a common table expression (CTE) or a selectable stored procedure.Multiple sources may be combined in a JOIN, and multiple result sets may be combined in a UNION.

The following sections discuss the available SELECT subclauses and their usage in detail.

FIRST, SKIP

Retrieves a slice of rows from an ordered set

Syntax
SELECT
  [FIRST <limit-expression>] [SKIP <limit-expression>]
  FROM ...
  ...

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)
Table 1. Arguments for the FIRST and SKIP Clauses
Argument Description

integer-literal

Integer literal

query-parameter

Query parameter place-holder.? in DSQL and :paramname in PSQL

value-expression

A valid expression (returning an integer value)
Note
FIRST and SKIP are non-standard syntax

FIRST and SKIP are Firebird-specific clauses.Use the SQL-standard [fblangref50-dml-select-offsetfetch] syntax wherever possible.

FIRST m limits the output of a query to the first m rows.SKIP n will skip the first n rows of the result set before returning rows.

FIRST and SKIP are both optional.When used together as in “FIRST m SKIP n”, the n topmost rows of the result set are discarded, and the first m rows of the rest of the set are returned.