|
 |
|
| |
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
]
[, ...] ]
[ FOR UPDATE [ OF
table
[, ...] ] ]
[ LIMIT {
count
| ALL } [ { OFFSET | , }
start
] ]
In this syntax diagram,
source
may be either a table name or a subselect.
The syntax for these general forms is as follows:
FROM { [ ONLY ]
table
[ [ AS ]
alias
[ (
column_alias
[, ...] ) ] ] |
(
query
) [ AS ]
alias
[ (
column_alias
[, ...] ) ] }
-
ALL
-
The ALL keyword may be specified as a noise term to make it
clear that all rows should be returned.
-
DISTINCT [ ON (
expression
[, ...] ) ]
-
The DISTINCT clause specifies a
column (or expression) for which to retrieve only one row per unique value of
expression
.
-
target
[ AS
name
] [, ...]
-
The SELECT targets are usually column names, though they can be constants, identifier,
function or general expression. Each
target
requested must be separated by commas, and may be named
dynamically to
name
via the AS clause. Supplying the asterisk symbol
(*) as a target is shorthand for requesting all non-system columns, and may be
listed along with other targets.
-
FROM
source
[, ...]
-
The FROM clause dictates the
source
that PostgreSQL will look in for the specified
targets
.
The
source
, in this case, may be a table name or a sub-query.
You can specify numerous sources, separated by commas. (This is roughly equivalent to a cross join).
The syntax for the FROM clause is described in more detail later in this section.
-
[ NATURAL ]
join_type
source
[ ON
condition
| USING (
column_list
) ]
-
The FROM sources may be joined together via the
JOIN clause, which requires a
join_type
(e.g.,
INNER, FULL OUTER,
CROSS) and may require a
condition
or
column_list
to further define the nature of the join, depending on the
join_type
.
-
WHERE
condition
-
The WHERE clause constrains the result set from the SELECT statement to specified
criteria, which are defined by
condition
. Conditions must return a single Boolean value (true
or false), but may consist of several checks combined with logical operators (e.g., with
AND, and OR) to indicate that available
rows must meet all supplied conditions to be included in the statement's results.
-
GROUP BY
expression
[, ...]
-
The GROUP BY clause aggregates (groups) rows together by the criteria described in
expression
. This can be as
simple as a column name (and often is) or an arbitrary expression applied to values of the result set.
-
HAVING
condition
[, ...]
-
The HAVING clause is similar to the WHERE clause, but checks its conditions on aggregated
(grouped) sets instead of atomic rows.
-
{ UNION | INTERSECT | EXCEPT } [ ALL ]
sub-query
-
Performs one of three
set operations
between the
SELECT statement and a second query, returning their result sets in uniform column
structure (which must be compatible).
-
UNION
-
Returns the set of collected rows.
-
INTERSECT
-
Returns the set of rows where the values of the two sets overlap.
-
EXCEPT
-
Returns the set of rows which are found in the SELECT
statement, but not found in the secondary query.
-
ORDER BY
expression
-
Sorts the results of the SELECT statement by
expression
.
-
[ ASC | DESC | USING operator ]
-
Determines whether or not the ORDER BY
expression
proceeds in ascending order (ASC), or descending order (DESC).
An
operator
may alternatively be specified with the USING keyword
(e.g., < or >).
-
FOR UPDATE [ OF
table
[, ...] ]
-
Allows for exclusive locking of the returned rows. When used within a transaction block, FOR UPDATE
locks the rows of the specified table until the transaction is committed. While locked, the rows cannot be updated by other transactions.
-
LIMIT {
count
| ALL }
-
Limits the number of rows returned to a maximum of
count
, or explicitly allows ALL rows.
-
{ OFFSET | , }
start
-
Instructs the LIMIT clause at what point to begin limiting the
results. For example, a LIMIT with a
count
set to 100, and
an OFFSET clause with a
start
value of 50
would return the rows from 50 to 150 (if there are that many results to return).
Terms used in the FROM clause's syntax description are as follows:
-
[ ONLY ]
table
-
The
table
name specifies what table to use as a source for the
SELECT statement. Specifying the ONLY
clause causes the rows of any child's table to be omitted from the query.
-
[ AS ]
alias
-
An
alias
may optionally be assigned to a FROM
source, in order to simplify a query (e.g., books might be temporarily referenced
with an alias of b). The AS term is considered
noise, and is optional.
-
(
query
) [ AS ]
alias
-
Any valid SELECT statement may be placed in parentheses as the
query
. This causes the result set created by the query to be used as a
FROM source, as if it had been a static table. This use of a sub-query requires a specified
alias
.
-
(
column_alias
[, ...] )
-
The FROM sources which have assigned aliases may also alias columns by
specifying arbitrary column aliases. Each
column_alias
must be separated by commas, and grouped
within parentheses following the FROM source's alias. These aliases must match the
order of the defined columns in the table to which it is applied.
|
|
|
