queries.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.34 2005/05/08 03:08:05 momjian Exp $ -->

<chapter id="queries">
 <title>Queries</title>

 <indexterm zone="queries">
  <primary>query</primary>
 </indexterm>

 <indexterm zone="queries">
  <primary>SELECT</primary>
 </indexterm>

 <para>
  The previous chapters explained how to create tables, how to fill
  them with data, and how to manipulate that data.  Now we finally
  discuss how to retrieve the data out of the database.
 </para>


 <sect1 id="queries-overview">
  <title>Overview</title>

  <para>
   The process of retrieving or the command to retrieve data from a
   database is called a <firstterm>query</firstterm>.  In SQL the
   <command>SELECT</command> command is used to specify queries.  The
   general syntax of the <command>SELECT</command> command is
<synopsis>
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>
</synopsis>
   The following sections describe the details of the select list, the
   table expression, and the sort specification.
  </para>

  <para>
   A simple kind of query has the form
<programlisting>
SELECT * FROM table1;
</programlisting>
  Assuming that there is a table called <literal>table1</literal>,
  this command would retrieve all rows and all columns from
  <literal>table1</literal>.  (The method of retrieval depends on the
  client application.  For example, the
  <application>psql</application> program will display an ASCII-art
  table on the screen, while client libraries will offer functions to
  extract individual values from the query result.)  The select list
  specification <literal>*</literal> means all columns that the table
  expression happens to provide.  A select list can also select a
  subset of the available columns or make calculations using the
  columns.  For example, if
  <literal>table1</literal> has columns named <literal>a</>,
  <literal>b</>, and <literal>c</> (and perhaps others) you can make
  the following query:
<programlisting>
SELECT a, b + c FROM table1;
</programlisting>
  (assuming that <literal>b</> and <literal>c</> are of a numerical
  data type).
  See <xref linkend="queries-select-lists"> for more details.
 </para>

 <para>
  <literal>FROM table1</literal> is a particularly simple kind of
  table expression: it reads just one table.  In general, table
  expressions can be complex constructs of base tables, joins, and
  subqueries.  But you can also omit the table expression entirely and
  use the <command>SELECT</command> command as a calculator:
<programlisting>
SELECT 3 * 4;
</programlisting>
  This is more useful if the expressions in the select list return
  varying results.  For example, you could call a function this way:
<programlisting>
SELECT random();
</programlisting>
  </para>
 </sect1>


 <sect1 id="queries-table-expressions">
  <title>Table Expressions</title>

  <indexterm zone="queries-table-expressions">
   <primary>table expression</primary>
  </indexterm>

  <para>
   A <firstterm>table expression</firstterm> computes a table.  The
   table expression contains a <literal>FROM</> clause that is
   optionally followed by <literal>WHERE</>, <literal>GROUP BY</>, and
   <literal>HAVING</> clauses.  Trivial table expressions simply refer
   to a table on disk, a so-called base table, but more complex
   expressions can be used to modify or combine base tables in various
   ways.
  </para>

  <para>
   The optional <literal>WHERE</>, <literal>GROUP BY</>, and
   <literal>HAVING</> clauses in the table expression specify a
   pipeline of successive transformations performed on the table
   derived in the <literal>FROM</> clause.  All these transformations
   produce a virtual table that provides the rows that are passed to
   the select list to compute the output rows of the query.
  </para>
	
  <sect2 id="queries-from">
   <title>The <literal>FROM</literal> Clause</title>
 
   <para>
    The <xref linkend="sql-from" endterm="sql-from-title"> derives a
    table from one or more other tables given in a comma-separated
    table reference list.
<synopsis>
FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional>
</synopsis>

    A table reference may be a table name (possibly schema-qualified),
    or a derived table such as a subquery, a table join, or complex
    combinations of these.  If more than one table reference is listed
    in the <literal>FROM</> clause they are cross-joined (see below)
    to form the intermediate virtual table that may then be subject to
    transformations by the <literal>WHERE</>, <literal>GROUP BY</>,
    and <literal>HAVING</> clauses and is finally the result of the
    overall table expression.
   </para>

   <indexterm>
    <primary>ONLY</primary>
   </indexterm>

   <para>
    When a table reference names a table that is the supertable of a
    table inheritance hierarchy, the table reference produces rows of
    not only that table but all of its subtable successors, unless the
    key word <literal>ONLY</> precedes the table name.  However, the
    reference produces only the columns that appear in the named table
    &mdash; any columns added in subtables are ignored.
   </para>

   <sect3 id="queries-join">
    <title>Joined Tables</title>

    <indexterm zone="queries-join">
     <primary>join</primary>
    </indexterm>

    <para>
     A joined table is a table derived from two other (real or
     derived) tables according to the rules of the particular join
     type.  Inner, outer, and cross-joins are available.
    </para>

    <variablelist>
     <title>Join Types</title>

     <varlistentry>
      <term>Cross join</term>

      <indexterm>
       <primary>join</primary>
       <secondary>cross</secondary>
      </indexterm>

      <indexterm>
       <primary>cross join</primary>
      </indexterm>

      <listitem>
<synopsis>
<replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
</synopsis>

       <para>
        For each combination of rows from
        <replaceable>T1</replaceable> and
        <replaceable>T2</replaceable>, the derived table will contain a
        row consisting of all columns in <replaceable>T1</replaceable>
        followed by all columns in <replaceable>T2</replaceable>.  If
        the tables have N and M rows respectively, the joined
        table will have N * M rows.
       </para>

       <para>
        <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
        <replaceable>T2</replaceable></literal> is equivalent to
        <literal>FROM <replaceable>T1</replaceable>,
        <replaceable>T2</replaceable></literal>.  It is also equivalent to
        <literal>FROM <replaceable>T1</replaceable> INNER JOIN
        <replaceable>T2</replaceable> ON TRUE</literal> (see below).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualified joins</term>

      <indexterm>
       <primary>join</primary>
       <secondary>outer</secondary>
      </indexterm>

      <indexterm>
       <primary>outer join</primary>
      </indexterm>

      <listitem>
<synopsis>
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
<replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
</synopsis>
        
       <para>
        The words <literal>INNER</literal> and
        <literal>OUTER</literal> are optional in all forms.
        <literal>INNER</literal> is the default;
        <literal>LEFT</literal>, <literal>RIGHT</literal>, and
        <literal>FULL</literal> imply an outer join.
       </para>

       <para>
        The <firstterm>join condition</firstterm> is specified in the
        <literal>ON</> or <literal>USING</> clause, or implicitly by
        the word <literal>NATURAL</>.  The join condition determines
        which rows from the two source tables are considered to
        <quote>match</quote>, as explained in detail below.
       </para>

       <para>
        The <literal>ON</> clause is the most general kind of join
        condition: it takes a Boolean value expression of the same
        kind as is used in a <literal>WHERE</> clause.  A pair of rows
        from <replaceable>T1</> and <replaceable>T2</> match if the
        <literal>ON</> expression evaluates to true for them.
       </para>

       <para>
        <literal>USING</> is a shorthand notation: it takes a
        comma-separated list of column names, which the joined tables
        must have in common, and forms a join condition specifying
        equality of each of these pairs of columns.  Furthermore, the
        output of a <literal>JOIN USING</> has one column for each of
        the equated pairs of input columns, followed by all of the
        other columns from each table.  Thus, <literal>USING (a, b,
        c)</literal> is equivalent to <literal>ON (t1.a = t2.a AND
        t1.b = t2.b AND t1.c = t2.c)</literal> with the exception that
        if <literal>ON</> is used there will be two columns
        <literal>a</>, <literal>b</>, and <literal>c</> in the result,
        whereas with <literal>USING</> there will be only one of each.
       </para>

       <para>
        <indexterm>
	 <primary>join</primary>
	 <secondary>natural</secondary>
	</indexterm>
        <indexterm>
	 <primary>natural join</primary>
	</indexterm>
        Finally, <literal>NATURAL</> is a shorthand form of
        <literal>USING</>: it forms a <literal>USING</> list
        consisting of exactly those column names that appear in both
        input tables.  As with <literal>USING</>, these columns appear
        only once in the output table.
       </para>

       <para>
        The possible types of qualified join are:

       <variablelist>
        <varlistentry>
         <term><literal>INNER JOIN</></term>

         <listitem>
          <para>
           For each row R1 of T1, the joined table has a row for each
           row in T2 that satisfies the join condition with R1.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term><literal>LEFT OUTER JOIN</></term>

         <indexterm>
          <primary>join</primary>
          <secondary>left</secondary>
         </indexterm>

         <indexterm>
          <primary>left join</primary>
         </indexterm>

         <listitem>
          <para>
           First, an inner join is performed.  Then, for each row in
           T1 that does not satisfy the join condition with any row in
           T2, a joined row is added with null values in columns of
           T2.  Thus, the joined table unconditionally has at least
           one row for each row in T1.
          </para>
         </listitem>
        </varlistentry>
         
        <varlistentry>
         <term><literal>RIGHT OUTER JOIN</></term>

         <indexterm>
          <primary>join</primary>
          <secondary>right</secondary>
         </indexterm>

         <indexterm>
          <primary>right join</primary>
         </indexterm>

         <listitem>
          <para>
           First, an inner join is performed.  Then, for each row in
           T2 that does not satisfy the join condition with any row in
           T1, a joined row is added with null values in columns of
           T1.  This is the converse of a left join: the result table
           will unconditionally have a row for each row in T2.
          </para>
         </listitem>
        </varlistentry>
         
        <varlistentry>
         <term><literal>FULL OUTER JOIN</></term>

         <listitem>
          <para>
           First, an inner join is performed.  Then, for each row in
           T1 that does not satisfy the join condition with any row in