FirebirdSQL logo
 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.