6.1. SELECT

Used for

Retrieving data

Available in

DSQL, ESQL, PSQL

Global syntax

  1. SELECT
  2. [WITH [RECURSIVE] <cte> [, <cte> ...]]
  3. SELECT
  4.   [FIRST m] [SKIP n]
  5.   [DISTINCT | ALL] <columns>
  6. FROM
  7.   <source> [[AS] alias]
  8.   [<joins>]
  9. [WHERE <condition>]
  10. [GROUP BY <grouping-list>
  11. [HAVING <aggregate-condition>]]
  12. [PLAN <plan-expr>]
  13. [UNION [DISTINCT | ALL] <other-select>]
  14. [ORDER BY <ordering-list>]
  15. [ROWS <m> [TO <n>]]
  16. [FOR UPDATE [OF <columns>]]
  17. [WITH LOCK]
  18. [INTO <variables>]
  20. <variables> ::= [:]varname [, [:]varname ...]

Description

The SELECT statement retrieves data from the database and hands them to the application or the enclosing SQL statement. Data are 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 columns 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:

  1. select id, name, address
  2.   from contacts

Or, to retrieve all the columns:

  1. select * from sales

In practice, the rows retrieved are often limited by a WHERE clause. The result set may be sorted by an ORDER BY clause, and FIRST, SKIP or ROWS may further limit the number of output rows. The column list may contain all kinds of expressions instead of just 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 (SP). 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.