Home • Explore • Blog • CI
☰



postgresql
21th chunk of `doc/src/sgml/queries.sgml`
61ce70b1d551bdf06f6c8d74d950fa676c34be3046401bbb0000000100000fb7
 future key word additions, it is recommended that you always either
    write <literal>AS</literal> or double-quote the output column name.
   </para>

   <note>
    <para>
     The naming of output columns here is different from that done in
     the <literal>FROM</literal> clause (see <xref
     linkend="queries-table-aliases"/>).  It is possible
     to rename the same column twice, but the name assigned 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>ALL</primary>
    <secondary>SELECT ALL</secondary>
   </indexterm>
   <indexterm zone="queries-distinct">
    <primary>DISTINCT</primary>
    <secondary>SELECT DISTINCT</secondary>
   </indexterm>

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

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

   <indexterm>
    <primary>null value</primary>
    <secondary sortas="DISTINCT">in DISTINCT</secondary>
   </indexterm>

   <para>
    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</literal> filter.
    (<literal>DISTINCT ON</literal> processing occurs after <literal>ORDER
    BY</literal> sorting.)
   </para>

   <para>
    The <literal>DISTINCT ON</literal> 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</literal> and subqueries in <literal>FROM</literal>, this
    construct can be avoided, but it is often the most convenient
    alternative.
   </para>
  </sect2>
 </sect1>


 <sect1 id="queries-union">
  <title>Combining Queries (<literal>UNION</literal>, <literal>INTERSECT</literal>, <literal>EXCEPT</literal>)</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>
Title: SQL DISTINCT Clause and Set Operations
Summary
This section explains the DISTINCT clause in SQL SELECT statements and introduces set operations for combining query results. The DISTINCT keyword eliminates duplicate rows from the result set, with an option to use DISTINCT ON for more complex distinctness criteria. The text notes that NULL values are considered equal in DISTINCT comparisons. It also covers the set operations UNION, INTERSECT, and EXCEPT, which combine results from multiple queries. These operations allow for merging (UNION), finding common rows (INTERSECT), or removing rows (EXCEPT) between query results. The section explains the syntax for these operations and mentions the ALL option, which retains duplicate rows in UNION queries. It's noted that DISTINCT ON is not part of the SQL standard and can sometimes be replaced with GROUP BY or subqueries.