<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.24 2000/01/27 18:11:25 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.25 2000/02/21 01:13:52 tgl Exp $

Postgres documentation

-->

[ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]

[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]

[ { UNION [ ALL ] | INTERSECT | EXCEPT } <replaceable class="PARAMETER">select</replaceable> ]

[ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...] ]

[ FOR UPDATE [ OF class_name... ] ]

[LIMIT { count | ALL } [ { OFFSET | , }count ] ]

[ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC| USING <replaceable class="PARAMETER">operator</replaceable>] [, ...] ]

[ FOR UPDATE [ OF<replaceable class="PARAMETER">class_name</replaceable> [,...] ] ]

LIMIT {<replaceable class="PARAMETER">count</replaceable> | ALL } [ { OFFSET | , }<replaceable class="PARAMETER">start</replaceable> ]

</synopsis>

<refsect2 id="R2-SQL-SELECT-1">

<listitem>

<para>

Specifies another name for a column or an expression using

the AS clause.<replaceable class="PARAMETER">name</replaceable>

ORDER BY or GROUP BY clauses.

the AS clause. Thisname is primarily used to label the output

It can, however, be referenced in ORDER BY clauses.

</para>

</listitem>

</varlistentry>

</para>

<para>

The UNIONclause allows the result to be the collection of rows

The UNIONoperator allows the result to be the collection of rows

returned by the queries involved.

(See <xref linkend="sql-union" endterm="sql-union-title">.)

</para>

<para>

The INTERSECTgive you the rows that are common to both queries.

The INTERSECToperator gives you the rows that are common to both queries.

(See <xref linkend="sql-intersect" endterm="sql-intersect-title">.)

</para>

<para>

The EXCEPT give you the rows in the upper query not in the lower query.

The EXCEPT operator gives you the rows returned by the first query but

not the second query.

(See <xref linkend="sql-except" endterm="sql-except-title">.)

</para>

</para>

<para>

The LIMIT clause allows control over which rows are

returned by the query.

The LIMIT clause allows a subset of the rows produced by the query

to be returned to the user.

(See <xref linkend="sql-limit" endterm="sql-limit-title">.)

</para>

<para>

of the column. This feature makes it possible 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

assign a name

to a calculated column using the AS clause, e.g.:

to assign a name to a calculated column using the AS clause, e.g.:

<programlisting>

SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;

</programlisting></para>

<para>

From release 6.4 of PostgreSQL, the columns in the ORDER BY clause

do not need to appear in the SELECT clause.

From release 6.4 of PostgreSQL, it is also possible to ORDER BY

arbitrary expressions, including fields that do not appear in the

SELECT result list.

Thus the following statement is now legal:

<programlisting>

SELECT name FROM distributors ORDER BY code;

<para>

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.

If not specified, ASC is assumed by default. Alternatively, a

specific ordering operator name may be specified. ASC is equivalent

to USING '&lt;' and DESC is equivalent to USING '&gt;'.

</para>

</refsect2>

</para>

<para>

The UNIONclause allows the result to be the collection of rows

returned by the queries involved. (See UNION clause).

The twotables that represent the direct operands of the UNION must

have the same number of columns, and corresponding columns must be

The UNIONoperator allows the result to be the collection of rows

returned by the queries involved.

The twoSELECTs that represent the direct operands of the UNION must

produce the same number of columns, and corresponding columns must be

of compatible data types.

</para>

</para>

<para>

The INTERSECT clause allows the result to be all rows that are

common to the involved queries.

The two tables that represent the direct operands of the INTERSECT must

have the same number of columns, and corresponding columns must be

The INTERSECT operator gives you the rows that are common to both queries.

The two SELECTs that represent the direct operands of the INTERSECT must

produce the same number of columns, and corresponding columns must be

of compatible data types.

</para>

<para>

Multiple INTERSECT operators in the same SELECT statement are

evaluated left to right.

evaluated left to right, unless parentheses dictate otherwise.

</para>

</refsect2>

</para>

<para>

The EXCEPTclause allowstheresult to berowsfromtheupper query

that arenotinthelower query. (See EXCEPT clause).

The twotables that represent the direct operands of the EXCEPT must

have the same number of columns, and corresponding columns must be

The EXCEPToperator gives youthe rowsreturned bythefirst query but

not thesecond query.

The twoSELECTs that represent the direct operands of the EXCEPT must

produce the same number of columns, and corresponding columns must be

of compatible data types.

</para>

<para>

Multiple EXCEPT operators in the same SELECT statement are

evaluated left to right.

evaluated left to right, unless parentheses dictate otherwise.

</para>

</refsect2>

<refsect2 id="SQL-LIMIT">

<refsect2info>

<date>2000-02-20</date>

</refsect2info>

<title id="sql-limit-title">

LIMIT Clause

</title>

<para>

<synopsis>

LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } [ { OFFSET | , } <replaceable class="PARAMETER">start</replaceable> ]

OFFSET <replaceable class="PARAMETER">start</replaceable>

</synopsis>

where

<replaceable class="PARAMETER">count</replaceable> specifies the

maximum number of rows to return, and

<replaceable class="PARAMETER">start</replaceable> specifies the

number of rows to skip before starting to return rows.

</para>

<para>

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.

</para>

<para>

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

ordering? You don't know what ordering, unless you specified ORDER BY.

</para>

<para>

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 different subsets of a query

result <emphasis>will give inconsistent results</emphasis> unless

you enforce a predictable result ordering with ORDER BY. This is not

a bug; it is an inherent consequence 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.

</para>

</refsect2>

</refsect1>

This example shows how to obtain the union of the tables

<literal>distributors</literal> and

<literal>actors</literal>, restricting the results to those that begin

with letter W in each table. Only distinct rows areto be used, so the

with letter W in each table. Only distinct rows arewanted, so the

ALL keyword is omitted:

<programlisting>

parsing ambiguities

in this context.</para>

<para>

In the <acronym>SQL92</acronym> standard, the new column name

specified in an

"AS" clause may be referenced in GROUP BY and HAVING clauses.

This is not currently

allowed in <productname>Postgres</productname>.

</para>

<para>

The DISTINCT ON phrase is not part of <acronym>SQL92</acronym>.

Nor are LIMIT and OFFSET.

</para>

</refsect3>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.4 1999/07/22 15:09:14 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select_into.sgml,v 1.5 2000/02/21 01:13:52 tgl Exp $

Postgres documentation

-->

<date>1999-07-20</date>

</refsynopsisdivinfo>

<synopsis>

SELECT [ ALL | DISTINCT ] <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ] [, ...]

INTO [TEMP] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]

[ FROM <replaceable class="PARAMETER">table</replaceable> [<replaceable class="PARAMETER">alias</replaceable>] [, ...] ]

SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]

<replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ] [, ...]

[ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]

[ FROM <replaceable class="PARAMETER">table</replaceable> [ <replaceable class="PARAMETER">alias</replaceable> ] [, ...] ]

[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]

[ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]

[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]

[ { UNION [ALL] | INTERSECT | EXCEPT } <replaceable class="PARAMETER">select</replaceable>]

[ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...] ]

[ FOR UPDATE [OF class_name...]]

[LIMIT count [OFFSET|, count]]

[ { UNION [ALL] | INTERSECT | EXCEPT } <replaceable class="PARAMETER">select</replaceable>]

[ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC| USING <replaceable class="PARAMETER">operator</replaceable>] [, ...] ]

[ FOR UPDATE [OF<replaceable class="PARAMETER">class_name</replaceable> [,...] ]]

LIMIT{ <replaceable class="PARAMETER">count</replaceable> | ALL } [ {OFFSET | , } <replaceable class="PARAMETER">start</replaceable>]

</synopsis>

<refsect2 id="R2-SQL-SELECTINTO-1">