select.sgml

PostgreSQL7.4.6 for Linux

<!--
$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>