FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 CommentsProcedural SQL (PSQL) Statements 

DML — data manipulation language — is the subset of SQL that is used by applications and procedural modules to extract and change data.Extraction, for the purpose of reading data, both raw and manipulated, is achieved with the SELECT statement.INSERT is for adding new data and DELETE is for erasing data that is no longer required.UPDATE, MERGE and UPDATE OR INSERT all modify data in various ways.

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.

Characteristics of FIRST and SKIP

  • Any argument to FIRST and SKIP that is not an integer literal or an SQL parameter must be enclosed in parentheses.This implies that a subquery expression must be enclosed in two pairs of parentheses.

  • SKIP 0 is allowed but pointless.

  • FIRST 0 is also allowed and returns an empty set.

  • Negative SKIP and/or FIRST values result in an error.

  • If a SKIP lands past the end of the result set, an empty set is returned.

  • If the number of rows in the result set (or the remainder left after a SKIP) is less than the value of the m argument supplied for FIRST, that smaller number of rows is returned.These are valid results, not error conditions.