select.sgml

postgresql8.3.4源码,开源数据库

  </refsect2>

  <refsect2 id="SQL-EXCEPT">
   <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>

   <para>
    The <literal>EXCEPT</literal> clause has this general form:
<synopsis>
<replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
</synopsis>
    <replaceable class="parameter">select_statement</replaceable> is
    any <command>SELECT</command> statement without an <literal>ORDER
    BY</>, <literal>LIMIT</>, <literal>FOR UPDATE</literal>, or
    <literal>FOR SHARE</literal> clause.
   </para>

   <para>
    The <literal>EXCEPT</literal> operator computes the set of rows
    that are in the result of the left <command>SELECT</command>
    statement but not in the result of the right one.
   </para>
    
   <para>
    The result of <literal>EXCEPT</literal> does not contain any
    duplicate rows unless the <literal>ALL</> option is specified.
    With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
    left table and <replaceable>n</> duplicates in the right table will appear
    max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
   </para>
    
   <para>
    Multiple <literal>EXCEPT</literal> operators in the same
    <command>SELECT</command> statement are evaluated left to right,
    unless parentheses dictate otherwise.  <literal>EXCEPT</> binds at
    the same level as <literal>UNION</>.
   </para>
    
   <para>
    Currently, <literal>FOR UPDATE</> and <literal>FOR SHARE</> cannot be
    specified either for an <literal>EXCEPT</> result or for any input of
    an <literal>EXCEPT</>.
   </para>
  </refsect2>

  <refsect2 id="SQL-ORDERBY">
   <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>

   <para>
    The optional <literal>ORDER BY</literal> clause has this general form:
<synopsis>
ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
</synopsis>
    The <literal>ORDER BY</literal> clause causes the result rows to
    be sorted according to the specified expression(s).  If two rows are
    equal according to the leftmost expression, they are compared
    according to the next expression and so on.  If they are equal
    according to all specified expressions, they are returned in
    an implementation-dependent order.
   </para>

   <para>
    Each <replaceable class="parameter">expression</replaceable> can be the
    name or ordinal number of an output column
    (<command>SELECT</command> list item), or it can be an arbitrary
    expression formed from input-column values.
   </para>

   <para>
    The ordinal number refers to the ordinal (left-to-right) position
    of the result column. This feature makes it possible to define an
    ordering on the basis of a column that does not have a unique
    name.  This is never absolutely necessary because it is always
    possible to assign a name to a result column using the
    <literal>AS</> clause.
   </para>
    
   <para>
    It is also possible to use arbitrary expressions in the
    <literal>ORDER BY</literal> clause, including columns that do not
    appear in the <command>SELECT</command> result list.  Thus the
    following statement is valid:
<programlisting>
SELECT name FROM distributors ORDER BY code;
</programlisting>
    A limitation of this feature is that an <literal>ORDER BY</>
    clause applying to the result of a <literal>UNION</>,
    <literal>INTERSECT</>, or <literal>EXCEPT</> clause can only
    specify an output column name or number, not an expression.
   </para>

   <para>
    If an <literal>ORDER BY</> expression is a simple name that
    matches both a result column name and an input column name,
    <literal>ORDER BY</> will interpret it as the result column name.
    This is the opposite of the choice that <literal>GROUP BY</> will
    make in the same situation.  This inconsistency is made to be
    compatible with the SQL standard.
   </para>
    
   <para>
    Optionally one can add the key word <literal>ASC</> (ascending) or
    <literal>DESC</> (descending) after any expression in the
    <literal>ORDER BY</> clause.  If not specified, <literal>ASC</> is
    assumed by default.  Alternatively, a specific ordering operator
    name can be specified in the <literal>USING</> clause.
    An ordering operator must be a less-than or greater-than
    member of some B-tree operator family.
    <literal>ASC</> is usually equivalent to <literal>USING &lt;</> and
    <literal>DESC</> is usually equivalent to <literal>USING &gt;</>.
    (But the creator of a user-defined data type can define exactly what the
    default sort ordering is, and it might correspond to operators with other
    names.)
   </para>

   <para>
    If <literal>NULLS LAST</> is specified, null values sort after all
    non-null values; if <literal>NULLS FIRST</> is specified, null values
    sort before all non-null values.  If neither is specified, the default
    behavior is <literal>NULLS LAST</> when <literal>ASC</> is specified
    or implied, and <literal>NULLS FIRST</> when <literal>DESC</> is specified
    (thus, the default is to act as though nulls are larger than non-nulls).
    When <literal>USING</> is specified, the default nulls ordering depends
    on whether the operator is a less-than or greater-than operator.
   </para>

   <para>
    Note that ordering options apply only to the expression they follow;
    for example <literal>ORDER BY x, y DESC</> does not mean
    the same thing as <literal>ORDER BY x DESC, y DESC</>.
   </para>

   <para>
    Character-string data is sorted according to the locale-specific
    collation order that was established when the database cluster
    was initialized.
   </para>
  </refsect2>

  <refsect2 id="sql-distinct">
   <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>

   <para>
    If <literal>DISTINCT</> is specified, all duplicate rows are
    removed from the result set (one row is kept from each group of
    duplicates).  <literal>ALL</> specifies the opposite: all rows are
    kept; that is the default.
   </para>

   <para>
    <literal>DISTINCT ON ( <replaceable
    class="parameter">expression</replaceable> [, ...] )</literal>
    keeps only the first row of each set of rows where the given
    expressions evaluate to equal.  The <literal>DISTINCT ON</literal>
    expressions are interpreted using the same rules as for
    <literal>ORDER BY</> (see above).  Note that the <quote>first
    row</quote> of each set is unpredictable unless <literal>ORDER
    BY</> is used to ensure that the desired row appears first.  For
    example:
<programlisting>
SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
</programlisting>
    retrieves the most recent weather report for each location.  But
    if we had not used <literal>ORDER BY</> to force descending order
    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-LIMIT">
   <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>

   <para>
    The <literal>LIMIT</literal> clause consists of two independent
    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, while <replaceable
    class="parameter">start</replaceable> specifies the number of 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>
    When using <literal>LIMIT</>, it is a good idea to use an
    <literal>ORDER BY</> clause that constrains the result rows into a
    unique order.  Otherwise you will get an unpredictable subset of
    the query's rows &mdash; you might be asking for the tenth through
    twentieth rows, but tenth through twentieth in what ordering?  You
    don't know what ordering unless you specify <literal>ORDER BY</>.
   </para>

   <para>
    The query planner takes <literal>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 use
    for <literal>LIMIT</> and <literal>OFFSET</>.  Thus, using
    different <literal>LIMIT</>/<literal>OFFSET</> values to select
    different subsets of a query result <emphasis>will give
    inconsistent results</emphasis> unless you enforce a predictable
    result ordering with <literal>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
    <literal>ORDER BY</> is used to constrain the order.
   </para>

   <para>
    It is even possible for repeated executions of the same <literal>LIMIT</>
    query to return different subsets of the rows of a table, if there
    is not an <literal>ORDER BY</> to enforce selection of a deterministic
    subset.  Again, this is not a bug; determinism of the results is
    simply not guaranteed in such a case.
   </para>
  </refsect2>

  <refsect2 id="SQL-FOR-UPDATE-SHARE">
   <title id="sql-for-update-share-title"><literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal> Clause</title>

   <para>
    The <literal>FOR UPDATE</literal> clause has this form:
<synopsis>
FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
</synopsis>
   </para>

   <para>
    The closely related <literal>FOR SHARE</literal> clause has this form:
<synopsis>
FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
</synopsis>
   </para>

   <para>
    <literal>FOR UPDATE</literal> causes the rows retrieved by the
    <command>SELECT</command> statement to be locked as though for
    update.  This prevents them from being modified or deleted by
    other transactions until the current transaction ends.  That is,
    other transactions that attempt <command>UPDATE</command>,
    <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
    of these rows will be blocked until the current transaction ends.
    Also, if an <command>UPDATE</command>, <command>DELETE</command>,
    or <command>SELECT FOR UPDATE</command> from another transaction
    has already locked a selected row or rows, <command>SELECT FOR
    UPDATE</command> will wait for the other transaction to complete,
    and will then lock and return the updated row (or no row, if the
    row was deleted).  For further discussion see <xref
    linkend="mvcc">.
   </para>

   <para>
    To prevent the operation from waiting for other transactions to commit,
    use the <literal>NOWAIT</> option.  <command>SELECT FOR UPDATE
    NOWAIT</command> reports an error, rather than waiting, if a selected row
    cannot be locked immediately.  Note that <literal>NOWAIT</> applies only
    to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
    table-level lock is still taken in the ordinary way (see
    <xref linkend="mvcc">).  You can use the <literal>NOWAIT</> option of
    <xref linkend="sql-lock" endterm="sql-lock-title">
    if you need to acquire the table-level lock without waiting.
   </para>

   <para>
    <literal>FOR SHARE</literal> behaves similarly, except that it
    acquires a shared rather than exclusive lock on each retrieved
    row.  A shared lock blocks other transactions from performing
    <command>UPDATE</command>, <command>DELETE</command>, or <command>SELECT
    FOR UPDATE</command> on these rows, but it does not prevent them
    from performing <command>SELECT FOR SHARE</command>.
   </para>

   <para>
    If specific tables are named in <literal>FOR UPDATE</literal>
    or <literal>FOR SHARE</literal>,
    then only rows coming from those tables are locked; any other
    tables used in the <command>SELECT</command> are simply read as
    usual.  A <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
    clause without a table list affects all tables used in the command.
    If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is
    applied to a view or sub-query, it affects all tables used in
    the view or sub-query.
   </para>

   <para>
    Multiple <literal>FOR UPDATE</literal> and <literal>FOR SHARE</literal>