Practical PostgreSQL

Retrieving Rows with SELECT

The heart of all SQL queries is the SELECT command. SELECT is used to build queries (also known as SELECT statements). Queries are the only SQL instructions by which your data can be retrieved from tables and views. The data returned via a query is called a result set and consists of rows, with columns, similar to a table.

The columns of a result set are not stored on the disk in any fixed form. They are purely a temporary result of the query’s requested data. A query on a table may return a result set with the same column structure as the table, or it may differ drastically. Result sets may even have columns which are drawn from several other tables by a single query.

Since it is central to PostgreSQL, SELECT is easily the most complicated single command, having the most available clauses and parameters. The following is the syntax for SELECT. The terms used are summarized and described in greater detail within the following sections. The term expression is used to refer to either a column name, or a general expression (such as a column being operated upon by a constant, or another column).

SELECT [ ALL | DISTINCT [ ON ( expression  [, ...] ) ] ]
       target [ AS name ] [,...]
       [ FROM source [, ...] ]
              [ [ NATURAL ] join_type source
              [ ON condition | USING ( column_list ) ] ]
              [, ...]
       [ WHERE condition ]
       [ GROUP BY expression [, ...] ]
       [ HAVING condition [, ...] ]
       [ { UNION | INTERSECT | EXCEPT } [ ALL ] sub-query ]
       [ ORDER BY expression
               [ ASC | DESC | USING operator ...