queries.sgml

PostgreSQL7.4.6 for Linux

    the row's values substituted for any column references.  But the
    expressions in the select list do not have to reference any
    columns in the table expression of the <literal>FROM</> clause;
    they could be constant arithmetic expressions as well, for
    instance.
   </para>
  </sect2>

  <sect2 id="queries-column-labels">
   <title>Column Labels</title>

   <indexterm zone="queries-column-labels">
    <primary>alias</primary>
    <secondary>in the select list</secondary>
   </indexterm>

   <para>
    The entries in the select list can be assigned names for further
    processing.  The <quote>further processing</quote> in this case is
    an optional sort specification and the client application (e.g.,
    column headers for display).  For example:
<programlisting>
SELECT a AS value, b + c AS sum FROM ...
</programlisting>
   </para>

   <para>
    If no output column name is specified using <literal>AS</>, the system assigns a
    default name.  For simple column references, this is the name of the
    referenced column.  For function 
    calls, this is the name of the function.  For complex expressions,
    the system will generate a generic name.
   </para>

   <note>
    <para>
     The naming of output columns here is different from that done in
     the <literal>FROM</> clause (see <xref
     linkend="queries-table-aliases">).  This pipeline will in fact
     allow you to rename the same column twice, but the name chosen in
     the select list is the one that will be passed on.
    </para>
   </note>
  </sect2>

  <sect2 id="queries-distinct">
   <title><literal>DISTINCT</literal></title>

   <indexterm zone="queries-distinct">
    <primary>DISTINCT</primary>
   </indexterm>

   <indexterm zone="queries-distinct">
    <primary>duplicates</primary>
   </indexterm>

   <para>
    After the select list has been processed, the result table may
    optionally be subject to the elimination of duplicates.  The
    <literal>DISTINCT</literal> key word is written directly after the
    <literal>SELECT</literal> to enable this:
<synopsis>
SELECT DISTINCT <replaceable>select_list</replaceable> ...
</synopsis>
    (Instead of <literal>DISTINCT</> the word <literal>ALL</literal>
    can be used to select the default behavior of retaining all rows.)
   </para>

   <para>
    <indexterm><primary>null value</><secondary sortas="DISTINCT">in
    DISTINCT</></indexterm>
    Obviously, two rows are considered distinct if they differ in at
    least one column value.  Null values are considered equal in this
    comparison.
   </para>

   <para>
    Alternatively, an arbitrary expression can determine what rows are
    to be considered distinct:
<synopsis>
SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ...
</synopsis>
    Here <replaceable>expression</replaceable> is an arbitrary value
    expression that is evaluated for all rows.  A set of rows for
    which all the expressions are equal are considered duplicates, and
    only the first row of the set is kept in the output.  Note that
    the <quote>first row</quote> of a set is unpredictable unless the
    query is sorted on enough columns to guarantee a unique ordering
    of the rows arriving at the <literal>DISTINCT</> filter.
    (<literal>DISTINCT ON</> processing occurs after <literal>ORDER
    BY</> sorting.)
   </para>

   <para>
    The <literal>DISTINCT ON</> clause is not part of the SQL standard
    and is sometimes considered bad style because of the potentially
    indeterminate nature of its results.  With judicious use of
    <literal>GROUP BY</> and subqueries in <literal>FROM</> the
    construct can be avoided, but it is often the most convenient
    alternative.
   </para>
  </sect2>
 </sect1>


 <sect1 id="queries-union">
  <title>Combining Queries</title>

  <indexterm zone="queries-union">
   <primary>UNION</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>INTERSECT</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>EXCEPT</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>set union</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>set intersection</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>set difference</primary>
  </indexterm>
  <indexterm zone="queries-union">
   <primary>set operation</primary>
  </indexterm>

  <para>
   The results of two queries can be combined using the set operations
   union, intersection, and difference.  The syntax is
<synopsis>
<replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
<replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
<replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
</synopsis>
   <replaceable>query1</replaceable> and
   <replaceable>query2</replaceable> are queries that can use any of
   the features discussed up to this point.  Set operations can also
   be nested and chained, for example
<synopsis>
<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> UNION <replaceable>query3</replaceable>
</synopsis>
   which really says
<synopsis>
(<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) UNION <replaceable>query3</replaceable>
</synopsis>
  </para>

  <para>
   <literal>UNION</> effectively appends the result of
   <replaceable>query2</replaceable> to the result of
   <replaceable>query1</replaceable> (although there is no guarantee
   that this is the order in which the rows are actually returned).
   Furthermore, it eliminates all duplicate rows, in the sense of
   <literal>DISTINCT</>, unless <literal>UNION ALL</> is used.
  </para>

  <para>
   <literal>INTERSECT</> returns all rows that are both in the result
   of <replaceable>query1</replaceable> and in the result of
   <replaceable>query2</replaceable>.  Duplicate rows are eliminated
   unless <literal>INTERSECT ALL</> is used.
  </para>

  <para>
   <literal>EXCEPT</> returns all rows that are in the result of
   <replaceable>query1</replaceable> but not in the result of
   <replaceable>query2</replaceable>.  (This is sometimes called the
   <firstterm>difference</> between two queries.)  Again, duplicates
   are eliminated unless <literal>EXCEPT ALL</> is used.
  </para>

  <para>
   In order to calculate the union, intersection, or difference of two
   queries, the two queries must be <quote>union compatible</quote>,
   which means that they both return the same number of columns, and
   that the corresponding columns have compatible data types, as
   described in <xref linkend="typeconv-union-case">.
  </para>
 </sect1>


 <sect1 id="queries-order">
  <title>Sorting Rows</title>

  <indexterm zone="queries-order">
   <primary>sorting</primary>
  </indexterm>

  <indexterm zone="queries-order">
   <primary>ORDER BY</primary>
  </indexterm>
	   
  <para>
   After a query has produced an output table (after the select list
   has been processed) it can optionally be sorted.  If sorting is not
   chosen, the rows will be returned in random order.  The actual
   order in that case will depend on the scan and join plan types and
   the order on disk, but it must not be relied on.  A particular
   output ordering can only be guaranteed if the sort step is explicitly
   chosen.
  </para>

  <para>
   The <literal>ORDER BY</> clause specifies the sort order:
<synopsis>
SELECT <replaceable>select_list</replaceable>
    FROM <replaceable>table_expression</replaceable>
    ORDER BY <replaceable>column1</replaceable> <optional>ASC | DESC</optional> <optional>, <replaceable>column2</replaceable> <optional>ASC | DESC</optional> ...</optional>
</synopsis>
   <replaceable>column1</replaceable>, etc., refer to select list
   columns.  These can be either the output name of a column (see
   <xref linkend="queries-column-labels">) or the number of a column.  Some
   examples:
<programlisting>
SELECT a, b FROM table1 ORDER BY a;
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;
</programlisting>
  </para>

  <para>
   As an extension to the SQL standard, <productname>PostgreSQL</productname> also allows ordering
   by arbitrary expressions:
<programlisting>
SELECT a, b FROM table1 ORDER BY a + b;
</programlisting>
   References to column names in the <literal>FROM</> clause that are
   renamed in the select list are also allowed:
<programlisting>
SELECT a AS b FROM table1 ORDER BY a;
</programlisting>
   But these extensions do not work in queries involving
   <literal>UNION</>, <literal>INTERSECT</>, or <literal>EXCEPT</>,
   and are not portable to other SQL databases.
  </para>

  <para>
   Each column specification may be followed by an optional
   <literal>ASC</> or <literal>DESC</> to set the sort direction to
   ascending or descending.  <literal>ASC</> order is the default.
   Ascending order puts smaller values first, where
   <quote>smaller</quote> is defined in terms of the
   <literal>&lt;</literal> operator.  Similarly, descending order is
   determined with the <literal>&gt;</literal> operator.
    <footnote>
     <para>
      Actually, <productname>PostgreSQL</> uses the <firstterm>default B-tree
      operator class</> for the column's data type to determine the sort
      ordering for <literal>ASC</> and <literal>DESC</>.  Conventionally,
      data types will be set up so that the <literal>&lt;</literal> and
      <literal>&gt;</literal> operators correspond to this sort ordering,
      but a user-defined data type's designer could choose to do something
      different.
     </para>
    </footnote>
  </para>

  <para>
   If more than one sort column is specified, the later entries are
   used to sort rows that are equal under the order imposed by the
   earlier sort columns.
  </para>
 </sect1>


 <sect1 id="queries-limit">
  <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>

  <indexterm zone="queries-limit">
   <primary>LIMIT</primary>
  </indexterm>

  <indexterm zone="queries-limit">
   <primary>OFFSET</primary>
  </indexterm>

  <para>
   <literal>LIMIT</> and <literal>OFFSET</> allow you to retrieve just
   a portion of the rows that are generated by the rest of the query:
<synopsis>
SELECT <replaceable>select_list</replaceable>
    FROM <replaceable>table_expression</replaceable>
    <optional>LIMIT { <replaceable>number</replaceable> | ALL }</optional> <optional>OFFSET <replaceable>number</replaceable></optional>
</synopsis>
  </para>

  <para>
   If a limit count is given, no more than that many rows will be
   returned (but possibly less, if the query itself yields less rows).
   <literal>LIMIT ALL</> is the same as omitting the <literal>LIMIT</>
   clause.
  </para>

  <para>
   <literal>OFFSET</> says to skip that many rows before beginning to
   return rows.  <literal>OFFSET 0</> is the same as
   omitting the <literal>OFFSET</> clause.  If both <literal>OFFSET</>
   and <literal>LIMIT</> appear, then <literal>OFFSET</> rows are
   skipped before starting to count the <literal>LIMIT</> rows that
   are returned.
  </para>

  <para>
   When using <literal>LIMIT</>, it is important 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. You may be asking for the tenth through
   twentieth rows, but tenth through twentieth in what ordering?  The
   ordering is unknown, unless you specified <literal>ORDER BY</>.
  </para>

  <para>
   The query optimizer 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 give
   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>
 </sect1>

</chapter>