<!--$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.71.2.2 2004/09/13 01:59:35 neilc Exp $PostgreSQL documentation--><refentry id="SQL-SELECT"> <refmeta>  <refentrytitle id="sql-select-title">SELECT</refentrytitle>  <refmiscinfo>SQL - Language Statements</refmiscinfo> </refmeta> <refnamediv>  <refname>SELECT</refname>  <refpurpose>retrieve rows from a table or view</refpurpose> </refnamediv> <indexterm zone="sql-select">  <primary>SELECT</primary> </indexterm> <refsynopsisdiv><synopsis>SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]    * | <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...]    [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]    [ WHERE <replaceable class="parameter">condition</replaceable> ]    [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]    [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]    [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]    [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]    [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]    [ OFFSET <replaceable class="parameter">start</replaceable> ]    [ FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] ]where <replaceable class="parameter">from_item</replaceable> can be one of:    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]    ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]    <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]    <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )    <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> </refsynopsisdiv> <refsect1>  <title>Description</title>  <para>   <command>SELECT</command> retrieves rows from one or more tables.   The general processing of <command>SELECT</command> is as follows:   <orderedlist>    <listitem>     <para>      All elements in the <literal>FROM</literal> list are computed.      (Each element in the <literal>FROM</literal> list is a real or      virtual table.)  If more than one element is specified in the      <literal>FROM</literal> list, they are cross-joined together.      (See <xref linkend="sql-from" endterm="sql-from-title"> below.)     </para>    </listitem>    <listitem>     <para>      If the <literal>WHERE</literal> clause is specified, all rows      that do not satisfy the condition are eliminated from the      output.  (See <xref linkend="sql-where"      endterm="sql-where-title"> below.)     </para>    </listitem>    <listitem>     <para>      If the <literal>GROUP BY</literal> clause is specified, the      output is divided into groups of rows that match on one or more      values.  If the <literal>HAVING</literal> clause is present, it      eliminates groups that do not satisfy the given condition.  (See      <xref linkend="sql-groupby" endterm="sql-groupby-title"> and      <xref linkend="sql-having" endterm="sql-having-title"> below.)     </para>    </listitem>    <listitem>     <para>      Using the operators <literal>UNION</literal>,      <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the      output of more than one <command>SELECT</command> statement can      be combined to form a single result set.  The      <literal>UNION</literal> operator returns all rows that are in      one or both of the result sets.  The      <literal>INTERSECT</literal> operator returns all rows that are      strictly in both result sets.  The <literal>EXCEPT</literal>      operator returns the rows that are in the first result set but      not in the second.  In all three cases, duplicate rows are      eliminated unless <literal>ALL</literal> is specified. (See      <xref linkend="sql-union" endterm="sql-union-title">, <xref      linkend="sql-intersect" endterm="sql-intersect-title">, and      <xref linkend="sql-except" endterm="sql-except-title"> below.)     </para>    </listitem>    <listitem>     <para>      The actual output rows are computed using the      <command>SELECT</command> output expressions for each selected      row.  (See      <xref linkend="sql-select-list" endterm="sql-select-list-title">      below.)     </para>    </listitem>    <listitem>     <para>      If the <literal>ORDER BY</literal> clause is specified, the      returned rows are sorted in the specified order.  If      <literal>ORDER BY</literal> is not given, the rows are returned      in whatever order the system finds fastest to produce.  (See      <xref linkend="sql-orderby" endterm="sql-orderby-title"> below.)     </para>    </listitem>    <listitem>     <para>      <literal>DISTINCT</literal> eliminates duplicate rows from the      result.  <literal>DISTINCT ON</literal> eliminates rows that      match on all the specified expressions.  <literal>ALL</literal>      (the default) will return all candidate rows, including      duplicates.  (See <xref linkend="sql-distinct"      endterm="sql-distinct-title"> below.)     </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      <command>SELECT</command> statement to lock the selected rows      against concurrent updates.  (See <xref linkend="sql-for-update"      endterm="sql-for-update-title"> below.)     </para>    </listitem>   </orderedlist>  </para>     <para>   You must have <literal>SELECT</literal> privilege on a table to   read its values.  The use of <literal>FOR UPDATE</literal> requires   <literal>UPDATE</literal> privilege as well.  </para> </refsect1> <refsect1>  <title>Parameters</title>  <refsect2 id="SQL-FROM">   <title id="sql-from-title"><literal>FROM</literal> Clause</title>   <para>    The <literal>FROM</literal> clause specifies one or more source    tables for the <command>SELECT</command>.  If multiple sources are    specified, the result is the Cartesian product (cross join) of all    the sources.  But usually qualification conditions    are added to restrict the returned rows to a small subset of the    Cartesian product.   </para>   <para>    <literal>FROM</literal>-clause elements can contain:    <variablelist>     <varlistentry>      <term><replaceable class="parameter">table_name</replaceable></term>      <listitem>       <para>        The name (optionally schema-qualified) of an existing table or        view.  If <literal>ONLY</> is specified, only that table is        scanned.  If <literal>ONLY</> is not specified, the table and        all its descendant tables (if any) are scanned.  <literal>*</>        can be appended to the table name to indicate that descendant        tables are to be scanned, but in the current version, this is        the default behavior.  (In releases before 7.1,        <literal>ONLY</> was the default behavior.)  The default        behavior can be modified by changing the        <varname>sql_inheritance</varname> configuration option.       </para>      </listitem>     </varlistentry>          <varlistentry>      <term><replaceable class="parameter">alias</replaceable></term>      <listitem>       <para>        A substitute name for the <literal>FROM</> item containing the        alias.  An alias is used for brevity or to eliminate ambiguity        for self-joins (where the same table is scanned multiple        times).  When an alias is provided, it completely hides the        actual name of the table or function; for example given        <literal>FROM foo AS f</>, the remainder of the        <command>SELECT</command> must refer to this <literal>FROM</>        item as <literal>f</> not <literal>foo</>.  If an alias is        written, a column alias list can also be written to provide        substitute names for one or more columns of the table.       </para>      </listitem>     </varlistentry>          <varlistentry>      <term><replaceable class="parameter">select</replaceable></term>      <listitem>       <para>        A sub-<command>SELECT</command> can appear in the        <literal>FROM</literal> clause.  This acts as though its        output were created as a temporary table for the duration of        this single <command>SELECT</command> command.  Note that the        sub-<command>SELECT</command> must be surrounded by        parentheses, and an alias <emphasis>must</emphasis> be        provided for it.       </para>      </listitem>     </varlistentry>     <varlistentry>      <term><replaceable class="parameter">function_name</replaceable></term>      <listitem>       <para>        Function calls can appear in the <literal>FROM</literal>        clause.  (This is especially useful for functions that return        result sets, but any function can be used.)  This acts as        though its output were created as a temporary table for the        duration of this single <command>SELECT</command> command. An        alias may also be used. If an alias is written, a column alias        list can also be written to provide substitute names for one        or more attributes of the function's composite return type. If        the function has been defined as returning the <type>record</>        data type, then an alias or the key word <literal>AS</> must        be present, followed by a column definition list in the form        <literal>( <replaceable        class="parameter">column_name</replaceable> <replaceable        class="parameter">data_type</replaceable> <optional>, ... </>        )</literal>.  The column definition list must match the actual        number and types of columns returned by the function.       </para>      </listitem>     </varlistentry>          <varlistentry>      <term><replaceable class="parameter">join_type</replaceable></term>      <listitem>       <para>        One of        <itemizedlist>         <listitem>          <para><literal>[ INNER ] JOIN</literal></para>         </listitem>         <listitem>          <para><literal>LEFT [ OUTER ] JOIN</literal></para>         </listitem>         <listitem>          <para><literal>RIGHT [ OUTER ] JOIN</literal></para>         </listitem>         <listitem>          <para><literal>FULL [ OUTER ] JOIN</literal></para>         </listitem>         <listitem>          <para><literal>CROSS JOIN</literal></para>         </listitem>        </itemizedlist>        For the <literal>INNER</> and <literal>OUTER</> join types, a        join condition must be specified, namely exactly one of        <literal>NATURAL</>, <literal>ON <replaceable        class="parameter">join_condition</replaceable></literal>, or        <literal>USING (<replaceable        class="parameter">join_column</replaceable> [, ...])</literal>.        See below for the meaning.  For <literal>CROSS JOIN</literal>,        none of these clauses may appear.       </para>       <para>        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 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.        These join types are just a notational convenience, since they        do nothing you couldn't do with plain <literal>FROM</> and        <literal>WHERE</>.       </para>       <para>        <literal>LEFT OUTER JOIN</> returns all rows in the qualified        Cartesian product (i.e., all combined rows that pass its join        condition), plus one copy of each row in the left-hand table        for which there was no right-hand row that passed the join        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</>        clause's own condition is considered while deciding which rows        have matches.  Outer conditions are applied afterwards.       </para>       <para>        Conversely, <literal>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        convenience, since you could convert it to a <literal>LEFT        OUTER JOIN</> by switching the left and right inputs.       </para>       <para>        <literal>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).       </para>      </listitem>     </varlistentry>          <varlistentry>      <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>      <listitem>       <para>        <replaceable class="parameter">join_condition</replaceable> is        an expression resulting in a value of type        <type>boolean</type> (similar to a <literal>WHERE</literal>        clause) that specifies which rows in a join are considered to        match.       </para>      </listitem>     </varlistentry>          <varlistentry>      <term><literal>USING (<replaceable class="parameter">join_column</replaceable> [, ...])</literal></term>      <listitem>       <para>        A clause of the form <literal>USING ( a, b, ... )</literal> is        shorthand for <literal>ON left_table.a = right_table.a AND        left_table.b = right_table.b ...</literal>.  Also,        <literal>USING</> implies that only one of each pair of        equivalent columns will be included in the join output, not        both.       </para>      </listitem>     </varlistentry>     <varlistentry>      <term><literal>NATURAL</literal></term>