SELECT - Retrieves rows from a table or view
SYNOPSIS
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
where from_item can be:
[ ONLY ] table_name [ * ]
[ [ AS ] alias [ ( column_alias_list ) ] ]
|
( select )
[ AS ] alias [ ( column_alias_list ) ]
|
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column_list ) ]
INPUTS
expression
The name of a table's column or an expression.
output_name
Specifies another name for an output column using
the AS clause. This name is primarily used to label
the column for display. It can also be used to
refer to the column's value in ORDER BY and GROUP
BY clauses. But the output_name cannot be used in
the WHERE or HAVING clauses; write out the expres
sion instead.
from_item
A table reference, sub-SELECT, or JOIN clause. See
below for details.
condition
A boolean expression giving a result of true or
false. See the WHERE and HAVING clause descrip
tions below.
select A select statement with all features except the
ORDER BY, FOR UPDATE, and LIMIT clauses (even those
can be used when the select is parenthesized).
table_name
The name of an existing table or view. If ONLY is
specified, only that table is scanned. If ONLY is
not specified, the table and all its descendant
tables (if any) are scanned. * can be appended to
the table name to indicate that descendant tables
are to be scanned, but as of Postgres 7.1 this is
the default behavior. (In releases before 7.1, ONLY
was the default behavior.)
alias A substitute name for the preceding table_name. An
alias is used for brevity or to eliminate ambiguity
for self-joins (where the same table is scanned
multiple times). If an alias is written, a column
alias list can also be written to provide substi
tute names for one or more columns of the table.
select A sub-SELECT can appear in the FROM clause. This
acts as though its output were created as a tempo
rary table for the duration of this single SELECT
command. Note that the sub-SELECT must be sur
rounded by parentheses, and an alias must be pro
vided for it.
join_type
One of [ INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [
OUTER ] JOIN, FULL [ OUTER ] JOIN, or CROSS JOIN.
For INNER and OUTER join types, exactly one of NAT
URAL, ON join_condition, or USING ( join_col
umn_list ) must appear. For CROSS JOIN, none of
these items may appear.
join_condition
A qualification condition. This is similar to the
WHERE condition except that it only applies to the
two from_items being joined in this JOIN clause.
join_column_list
A USING column list ( a, b, ... ) is shorthand for
the ON condition left_table.a = right_table.a AND
left_table.b = right_table.b ...
OUTPUTS
Rows The complete set of rows resulting from the query
specification.
count The count of rows returned by the query.
DESCRIPTION
SELECT will return rows from one or more tables.
condition; if WHERE is omitted, all rows are candidates.
(See WHERE Clause [select(l)].)
Actually, the returned rows are not directly the rows pro
duced by the FROM/WHERE/GROUP BY/HAVING clauses; rather,
the output rows are formed by computing the SELECT output
expressions for each selected row. * can be written in
the output list as a shorthand for all the columns of the
selected rows. Also, one can write table_name.* as a
shorthand for the columns coming from just that table.
DISTINCT will eliminate duplicate rows from the result.
ALL (the default) will return all candidate rows, includ
ing duplicates.
DISTINCT ON eliminates rows that match on all the speci
fied expressions, keeping only the first row of each set
of duplicates. The DISTINCT ON expressions are interpreted
using the same rules as for ORDER BY items; see below.
Note that "the first row" of each set is unpredictable
unless ORDER BY is used to ensure that the desired row
appears first. For example,
SELECT DISTINCT ON (location) location, time, report
FROM weatherReports
ORDER BY location, time DESC;
retrieves the most recent weather report for each loca
tion. But if we had not used ORDER BY to force descending
order of time values for each location, we'd have gotten a
report of unpredictable age for each location.
The GROUP BY clause allows a user to divide a table into
groups of rows that match on one or more values. (See
GROUP BY Clause [select(l)].)
The HAVING clause allows selection of only those groups of
rows meeting the specified condition. (See HAVING Clause
[select(l)].)
The ORDER BY clause causes the returned rows to be sorted
in a specified order. If ORDER BY is not given, the rows
are returned in whatever order the system finds cheapest
to produce. (See ORDER BY Clause [select(l)].)
SELECT queries can be combined using UNION, INTERSECT, and
EXCEPT operators. Use parentheses if necessary to deter
mine the ordering of these operators.
The UNION operator computes the collection of rows
returned by the queries involved. Duplicate rows are
eliminated unless ALL is specified. (See UNION Clause
The INTERSECT operator computes the rows that are common
to both queries. Duplicate rows are eliminated unless ALL
is specified. (See INTERSECT Clause [select(l)].)
The EXCEPT operator computes the rows returned by the
first query but not the second query. Duplicate rows are
eliminated unless ALL is specified. (See EXCEPT Clause
[select(l)].)
The FOR UPDATE clause allows the SELECT statement to per
form exclusive locking of selected rows.
The LIMIT clause allows a subset of the rows produced by
the query to be returned to the user. (See LIMIT Clause
[select(l)].)
You must have SELECT privilege to a table to read its val
ues (See the GRANT/REVOKE statements).
FROM CLAUSE
The FROM clause specifies one or more source tables for
the SELECT. If multiple sources are specified, the result
is conceptually the Cartesian product of all the rows in
all the sources --- but usually qualification conditions
are added to restrict the returned rows to a small subset
of the Cartesian product.
When a FROM item is a simple table name, it implicitly
includes rows from sub-tables (inheritance children) of
the table. ONLY will suppress rows from sub-tables of the
table. Before Postgres 7.1, this was the default result,
and adding sub-tables was done by appending * to the table
name. This old behaviour is available via the command SET
SQL_Inheritance TO OFF;
A FROM item can also be a parenthesized sub-SELECT (note
that an alias clause is required for a sub-SELECT!). This
is an extremely handy feature since it's the only way to
get multiple levels of grouping, aggregation, or sorting
in a single query.
Finally, a FROM item can be a JOIN clause, which combines
two simpler FROM items. (Use parentheses if necessary to
determine the order of nesting.)
A CROSS JOIN or INNER JOIN is a simple Cartesian product,
the same as you get from listing the two items at the top
level of FROM. CROSS JOIN is equivalent to INNER JOIN ON
(TRUE), that is, no rows are removed by qualification.
These join types are just a notational convenience, since
they do nothing you couldn't do with plain FROM and WHERE.
sian product (i.e., all combined rows that pass its ON
condition), plus one copy of each row in the left-hand
table for which there was no right-hand row that passed
the ON condition. This left-hand row is extended to the
full width of the joined table by inserting NULLs for the
right-hand columns. Note that only the JOIN's own ON or
USING condition is considered while deciding which rows
have matches. Outer ON or WHERE conditions are applied
afterwards.
Conversely, RIGHT OUTER JOIN returns all the joined rows,
plus one row for each unmatched right-hand row (extended
with nulls on the left). This is just a notational conve
nience, since you could convert it to a LEFT OUTER JOIN by
switching the left and right inputs.
FULL OUTER JOIN returns all the joined rows, plus one row
for each unmatched left-hand row (extended with nulls on
the right), plus one row for each unmatched right-hand row
(extended with nulls on the left).
For all the JOIN types except CROSS JOIN, you must write
exactly one of ON join_condition, USING ( join_column_list
), or NATURAL. ON is the most general case: you can write
any qualification expression involving the two tables to
be joined. A USING column list ( a, b, ... ) is shorthand
for the ON condition left_table.a = right_table.a AND
left_table.b = right_table.b ... Also, USING implies that
only one of each pair of equivalent columns will be
included in the JOIN output, not both. NATURAL is short
hand for a USING list that mentions all similarly-named
columns in the tables.
WHERE CLAUSE
The optional WHERE condition has the general form:
WHERE boolean_expr
boolean_expr can consist of any expression which evaluates
to a boolean value. In many cases, this expression will
be:
expr cond_op expr
or
log_op expr
where cond_op can be one of: =, <, <=, >, >= or <>, a con
ditional operator like ALL, ANY, IN, LIKE, or a locally
SELECT will ignore all rows for which the WHERE condition
does not return TRUE.
GROUP BY CLAUSE
GROUP BY specifies a grouped table derived by the applica
tion of this clause:
GROUP BY expression [, ...]
GROUP BY will condense into a single row all selected rows
that share the same values for the grouped columns. Aggre
gate functions, if any, are computed across all rows mak
ing up each group, producing a separate value for each
group (whereas without GROUP BY, an aggregate produces a
single value computed across all the selected rows). When
GROUP BY is present, it is not valid for the SELECT output
expression(s) to refer to ungrouped columns except within
aggregate functions, since there would be more than one
possible value to return for an ungrouped column.
A GROUP BY item can be an input column name, or the name
or ordinal number of an output column (SELECT expression),
or it can be an arbitrary expression formed from input-
column values. In case of ambiguity, a GROUP BY name will
be interpreted as an input-column name rather than an out
put column name.
HAVING CLAUSE
The optional HAVING condition has the general form:
HAVING boolean_expr
where boolean_expr is the same as specified for the WHERE
clause.
HAVING specifies a grouped table derived by the elimina
tion of group rows that do not satisfy the boolean_expr.
HAVING is different from WHERE: WHERE filters individual
rows before application of GROUP BY, while HAVING filters
group rows created by GROUP BY.
Each column referenced in boolean_expr shall unambiguously
reference a grouping column, unless the reference appears
within an aggregate function.
ORDER BY CLAUSE
ORDER BY expression [ ASC | DESC | USING operator ] [, ...]
output column (SELECT expression), or it can be an arbi
trary expression formed from input-column values. In case
of ambiguity, an ORDER BY name will be interpreted as an
output-column name.
The ordinal number refers to the ordinal (left-to-right)
position of the result column. This feature makes it pos
sible to define an ordering on the basis of a column that
does not have a proper name. This is never absolutely
necessary because it is always possible to assign a name
to a result column using the AS clause, e.g.:
SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;
It is also possible to ORDER BY arbitrary expressions (an
extension to SQL92), including fields that do not appear
in the SELECT result list. Thus the following statement
is legal:
SELECT name FROM distributors ORDER BY code;
A limitation of this feature is that an ORDER BY clause
applying to the result of a UNION, INTERSECT, or EXCEPT
query may only specify an output column name or number,
not an expression.
Note that if an ORDER BY item is a simple name that
matches both a result column name and an input column
name, ORDER BY will interpret it as the result column
name. This is the opposite of the choice that GROUP BY
will make in the same situation. This inconsistency is
mandated by the SQL92 standard.
Optionally one may add the keyword DESC (descending) or
ASC (ascending) after each column name in the ORDER BY
clause. If not specified, ASC is assumed by default.
Alternatively, a specific ordering operator name may be
specified. ASC is equivalent to USING < and DESC is equiv
alent to USING >.
UNION CLAUSE
table_query UNION [ ALL ] table_query
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
where table_query specifies any select expression without
an ORDER BY, FOR UPDATE, or LIMIT clause. (ORDER BY and
LIMIT can be attached to a sub-expression if it is
enclosed in parentheses. Without parentheses, these
not to its right-hand input expression.)
The UNION operator computes the collection (set union) of
the rows returned by the queries involved. The two
SELECTs that represent the direct operands of the UNION
must produce the same number of columns, and corresponding
columns must be of compatible data types.
The result of UNION does not contain any duplicate rows
unless the ALL option is specified. ALL prevents elimina
tion of duplicates.
Multiple UNION operators in the same SELECT statement are
evaluated left to right, unless otherwise indicated by
parentheses.
Currently, FOR UPDATE may not be specified either for a
UNION result or for the inputs of a UNION.
INTERSECT CLAUSE
table_query INTERSECT [ ALL ] table_query
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
where table_query specifies any select expression without
an ORDER BY, FOR UPDATE, or LIMIT clause.
INTERSECT is similar to UNION, except that it produces
only rows that appear in both query outputs, rather than
rows that appear in either.
The result of INTERSECT does not contain any duplicate
rows unless the ALL option is specified. With ALL, a row
that has m duplicates in L and n duplicates in R will
appear min(m,n) times.
Multiple INTERSECT operators in the same SELECT statement
are evaluated left to right, unless parentheses dictate
otherwise. INTERSECT binds more tightly than UNION ---
that is, A UNION B INTERSECT C will be read as A UNION (B
INTERSECT C) unless otherwise specified by parentheses.
EXCEPT CLAUSE
table_query EXCEPT [ ALL ] table_query
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
where table_query specifies any select expression without
an ORDER BY, FOR UPDATE, or LIMIT clause.
EXCEPT is similar to UNION, except that it produces only
right query's output.
The result of EXCEPT does not contain any duplicate rows
unless the ALL option is specified. With ALL, a row that
has m duplicates in L and n duplicates in R will appear
max(m-n,0) times.
Multiple EXCEPT operators in the same SELECT statement are
evaluated left to right, unless parentheses dictate other
wise. EXCEPT binds at the same level as UNION.
LIMIT CLAUSE
LIMIT { count | ALL } [ { OFFSET | , } start ]
OFFSET start
where count specifies the maximum number of rows to
return, and start specifies the number of rows to skip
before starting to return rows.
LIMIT allows you to retrieve just a portion of the rows
that are generated by the rest of the query. If a limit
count is given, no more than that many rows will be
returned. If an offset is given, that many rows will be
skipped before starting to return rows.
When using LIMIT, it is a good idea to use an ORDER BY
clause that constrains the result rows into a unique
order. Otherwise you will get an unpredictable subset of
the query's rows---you may be asking for the tenth through
twentieth rows, but tenth through twentieth in what order
ing? You don't know what ordering, unless you specified
ORDER BY.
As of Postgres 7.0, the query optimizer takes LIMIT into
account when generating a query plan, so you are very
likely to get different plans (yielding different row
orders) depending on what you give for LIMIT and OFFSET.
Thus, using different LIMIT/OFFSET values to select dif
ferent subsets of a query result will give inconsistent
results unless you enforce a predictable result ordering
with ORDER BY. This is not a bug; it is an inherent conse
quence of the fact that SQL does not promise to deliver
the results of a query in any particular order unless
ORDER BY is used to constrain the order.
USAGE
To join the table films with the table distributors:
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did
---------------------------+-----+------------------+------------+----------
The Third Man | 101 | British Lion | 1949-12-23 | Drama
The African Queen | 101 | British Lion | 1951-08-11 | Romantic
Une Femme est une Femme | 102 | Jean Luc Godard | 1961-03-12 | Romantic
Vertigo | 103 | Paramount | 1958-11-14 | Action
Becket | 103 | Paramount | 1964-02-03 | Drama
48 Hrs | 103 | Paramount | 1982-10-22 | Action
War and Peace | 104 | Mosfilm | 1967-02-12 | Drama
West Side Story | 105 | United Artists | 1961-01-03 | Musical
Bananas | 105 | United Artists | 1971-07-13 | Comedy
Yojimbo | 106 | Toho | 1961-06-16 | Drama
There's a Girl in my Soup | 107 | Columbia | 1970-06-11 | Comedy
Taxi Driver | 107 | Columbia | 1975-05-15 | Action
Absence of Malice | 107 | Columbia | 1981-11-15 | Action
Storia di una donna | 108 | Westward | 1970-08-15 | Romantic
The King and I | 109 | 20th Century Fox | 1956-08-11 | Musical
Das Boot | 110 | Bavaria Atelier | 1981-11-11 | Drama
Bed Knobs and Broomsticks | 111 | Walt Disney | | Musical
(17 rows)
To sum the column len of all films and group the results
by kind:
SELECT kind, SUM(len) AS total FROM films GROUP BY kind;
kind | total
----------+-------
Action | 07:34
Comedy | 02:58
Drama | 14:28
Musical | 06:42
Romantic | 04:38
(5 rows)
To sum the column len of all films, group the results by
kind and show those group totals that are less than 5
hours:
SELECT kind, SUM(len) AS total
FROM films
GROUP BY kind
HAVING SUM(len) < INTERVAL '5 hour';
kind | total
----------+-------
Comedy | 02:58
Romantic | 04:38
(2 rows)
The following two examples are identical ways of sorting
second column (name):
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;
did | name
-----+------------------
109 | 20th Century Fox
110 | Bavaria Atelier
101 | British Lion
107 | Columbia
102 | Jean Luc Godard
113 | Luso films
104 | Mosfilm
103 | Paramount
106 | Toho
105 | United Artists
111 | Walt Disney
112 | Warner Bros.
108 | Westward
(13 rows)
This example shows how to obtain the union of the tables
distributors and actors, restricting the results to those
that begin with letter W in each table. Only distinct rows
are wanted, so the ALL keyword is omitted:
distributors: actors:
did | name id | name
-----+-------------- ----+----------------
108 | Westward 1 | Woody Allen
111 | Walt Disney 2 | Warren Beatty
112 | Warner Bros. 3 | Walter Matthau
... ...
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%'
name
----------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen
EXTENSIONS
Postgres allows one to omit the FROM clause from a query.
This feature was retained from the original PostQuel query
language. It has a straightforward use to compute the
results of simple constant expressions:
SELECT 2+2;
?column?
----------
4
Some other DBMSes cannot do this except by introducing a
dummy one-row table to do the select from. A less obvious
use is to abbreviate a normal select from one or more
tables:
SELECT distributors.* WHERE name = 'Westward';
did | name
-----+----------
108 | Westward
This works because an implicit FROM item is added for each
table that is referenced in the query but not mentioned in
FROM. While this is a convenient shorthand, it's easy to
misuse. For example, the query
SELECT distributors.* FROM distributors d;
is probably a mistake; most likely the user meant
SELECT d.* FROM distributors d;
rather than the unconstrained join
SELECT distributors.* FROM distributors d, distributors distributors;
that he will actually get. To help detect this sort of
mistake, Postgres 7.1 and later will warn if the implicit-
FROM feature is used in a query that also contains an
explicit FROM clause.
SQL92
SELECT CLAUSE
In the SQL92 standard, the optional keyword "AS" is just
noise and can be omitted without affecting the meaning.
The Postgres parser requires this keyword when renaming
lead to parsing ambiguities in this context. "AS" is
optional in FROM items, however.
The DISTINCT ON phrase is not part of SQL92. Nor are
LIMIT and OFFSET.
In SQL92, an ORDER BY clause may only use result column
names or numbers, while a GROUP BY clause may only use
input column names. Postgres extends each of these
clauses to allow the other choice as well (but it uses the
standard's interpretation if there is ambiguity). Post
gres also allows both clauses to specify arbitrary expres
sions. Note that names appearing in an expression will
always be taken as input-column names, not as result-col
umn names.
UNION/INTERSECT/EXCEPT CLAUSE
The SQL92 syntax for UNION/INTERSECT/EXCEPT allows an
additional CORRESPONDING BY option:
table_query UNION [ALL]
[CORRESPONDING [BY (column [,...])]]
table_query
The CORRESPONDING BY clause is not supported by Postgres.
Man(1) output converted with
man2html