<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.71 2003/11/01 01:56:29 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.72 2003/11/04 00:34:45 tgl Exp $

PostgreSQL documentation

-->

<replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]

</synopsis>

<comment>FIXME: This last syntax is incorrect if the join type is an

INNER or OUTER join (in which case one of NATURAL, ON ..., or USING

... is mandatory, not optional). What's the best way to fix

this?</comment>

</refsynopsisdiv>

<refsect1>

<listitem>

<para>

The actual output rows are computed the

The actual output rows are computedusingthe

<command>SELECT</command> output expressions for each selected

row. (See

<xref linkend="sql-select-list" endterm="sql-select-list-title">

</para>

</listitem>

<listitem>

<para>

If the <literal>LIMIT</literal> or <literal>OFFSET</literal>

clause is specified, the <command>SELECT</command> statement

only returns a subset of the result rows. (See <xref

linkend="sql-limit" endterm="sql-limit-title"> below.)

</para>

</listitem>

<listitem>

<para>

<literal>DISTINCT</literal> eliminates duplicate rows from the

</para>

</listitem>

<listitem>

<para>

If the <literal>LIMIT</literal> or <literal>OFFSET</literal>

clause is specified, the <command>SELECT</command> statement

only returns a subset of the result rows. (See <xref

linkend="sql-limit" endterm="sql-limit-title"> below.)

</para>

</listitem>

<listitem>

<para>

The <literal>FOR UPDATE</literal> clause causes the

</para>

<para>

A <literal>JOIN</literal> clause, combines two

<literal>FROM</> items. (Use parentheses if necessary to

determine the order of nesting.)

A <literal>JOIN</literal> clause combines two

<literal>FROM</> items. Use parentheses if necessary to

determine the order of nesting. In the absence of parentheses,

<literal>JOIN</literal>s nest left-to-right. In any case

<literal>JOIN</literal> binds more tightly than the commas

separating <literal>FROM</> items.

</para>

<para>

<literal>CROSS JOIN</> and <literal>INNER JOIN</literal>

produce a simple Cartesian product, the same as you get from

listing the two items at the top level of <literal>FROM</>.

produce a simple Cartesian product, the same result as you get from

listing the two items at the top level of <literal>FROM</>,

but restricted by the join condition (if any).

<literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON

(true)</>, that is, no rows are removed by qualification.

(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 <literal>FROM</> and

<literal>WHERE</>.

condition. This left-hand row is extended to the full width

of the joined table by inserting null values for the

right-hand columns. Note that only the <literal>JOIN</>

clauses own condition is considered while deciding which rows

clause's own condition is considered while deciding which rows

have matches. Outer conditions are applied afterwards.

</para>

expressions. <replaceable

class="parameter">expression</replaceable> can be an input column

name, or the name or ordinal number of an output column

(<command>SELECT</command> list), or it can be an arbitrary

(<command>SELECT</command> list item), or an arbitrary

expression formed from input-column values. In case of ambiguity,

a <literal>GROUP BY</literal> name will be interpreted as an

input-column name rather than an output column name.

<para>

Currently, <literal>FOR UPDATE</> may not be specified either for

a <literal>UNION</> result or forthe inputs of <literal>UNION</>.

a <literal>UNION</> result or forany input of a <literal>UNION</>.

</para>

</refsect2>

</synopsis>

<replaceable class="parameter">expression</replaceable> can be the

name or ordinal number of an output column

(<command>SELECT</command> list), or it can be an arbitrary

(<command>SELECT</command> list item), or it can be an arbitrary

expression formed from input-column values.

</para>

equal according to the leftmost expression, the are compared

according to the next expression and so on. If they are equal

according to all specified expressions, they are returned in

random order.

an implementation-dependent order.

</para>

<para>

<para>

Optionally one may add the key word <literal>ASC</> (ascending) or

<literal>DESC</> (descending) aftereach expression in the

<literal>DESC</> (descending) afterany expression in the

<literal>ORDER BY</> clause. If not specified, <literal>ASC</> is

assumed by default. Alternatively, a specific ordering operator

name may be specified in the <literal>USING</> clause.

<para>

The <literal>LIMIT</literal> clause consists of two independent

clauses:

sub-clauses:

<synopsis>

LIMIT { <replaceable class="parameter">count</replaceable> | ALL }

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

</synopsis>

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

maximum number of rows to return,and <replaceable

maximum number of rows to return,while <replaceable

class="parameter">start</replaceable> specifies the number of rows

to skip before starting to return rows.

to skip before starting to return rows. When both are specified,

<replaceable class="parameter">start</replaceable> rows are skipped

before starting to count the <replaceable

class="parameter">count</replaceable> rows to be returned.

</para>

<para>

of time values for each location, we'd have gotten a report from

an unpredictable time for each location.

</para>

<para>

The <literal>DISTINCT ON</> expression(s) must match the leftmost

<literal>ORDER BY</> expression(s). The <literal>ORDER BY</> clause

will normally contain additional expression(s) that determine the

desired precedence of rows within each <literal>DISTINCT ON</> group.

</para>

</refsect2>

<refsect2 id="SQL-FOR-UPDATE">

</para>

<para>

This example shows how to obtain the union of the tables

The next 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 are wanted, so the

withtheletter W in each table. Only distinct rows are wanted, so the

key word <literal>ALL</literal> is omitted.

<programlisting>

<para>

This example shows how to use a function in the <literal>FROM</>

clause, both with and without a column definition list.

clause, both with and without a column definition list:

<programlisting>

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS '

</para>

<para>

SQL99 uses a slightly different definition which is not upward compatible

SQL99 uses a slightly different definition which is not entirely upward

compatible

with SQL92. In most cases, however, <productname>PostgreSQL</productname>

will interpret an <literal>ORDER BY</literal> or <literal>GROUP

BY</literal> expression the same way SQL99 does.