Home • Explore • Blog • CI
☰



postgresql
22th chunk of `doc/src/sgml/queries.sgml`
b235156dfd4d407194a9f0271b7b8d6bd891f6408af864dc0000000100000fa9

  </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>
<replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
<replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
</synopsis>
   where <replaceable>query1</replaceable> and
   <replaceable>query2</replaceable> are queries that can use any of
   the features discussed up to this point.
  </para>

  <para>
   <literal>UNION</literal> 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 duplicate rows from its result, in the same
   way as <literal>DISTINCT</literal>, unless <literal>UNION ALL</literal> is used.
  </para>

  <para>
   <literal>INTERSECT</literal> 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</literal> is used.
  </para>

  <para>
   <literal>EXCEPT</literal> 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</firstterm> between two queries.)  Again, duplicates
   are eliminated unless <literal>EXCEPT ALL</literal> 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 return the same number of columns and
   the corresponding columns have compatible data types, as
   described in <xref linkend="typeconv-union-case"/>.
  </para>

  <para>
   Set operations can be combined, for example
<synopsis>
<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> EXCEPT <replaceable>query3</replaceable>
</synopsis>
   which is equivalent to
<synopsis>
(<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) EXCEPT <replaceable>query3</replaceable>
</synopsis>
   As shown here, you can use parentheses to control the order of
   evaluation.  Without parentheses, <literal>UNION</literal>
   and <literal>EXCEPT</literal> associate left-to-right,
   but <literal>INTERSECT</literal> binds more tightly than those two
   operators.  Thus
<synopsis>
<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>
</synopsis>
   means
<synopsis>
<replaceable>query1</replaceable> UNION (<replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>)
</synopsis>
   You can also surround an individual <replaceable>query</replaceable>
   with parentheses.  This is important if
   the <replaceable>query</replaceable> needs to use any of the clauses
   discussed in following sections, such as <literal>LIMIT</literal>.
   Without parentheses,
Title: SQL Set Operations: UNION, INTERSECT, and EXCEPT
Summary
This section explains how to combine the results of multiple SQL queries using set operations: UNION, INTERSECT, and EXCEPT. UNION appends the results of two queries, eliminating duplicates unless UNION ALL is used. INTERSECT returns rows common to both query results, while EXCEPT returns rows from the first query that are not in the second. The queries must be 'union compatible', meaning they return the same number of columns with compatible data types. The text also covers the use of ALL to retain duplicates, the order of operations (INTERSECT binds more tightly than UNION and EXCEPT), and the use of parentheses to control evaluation order. It's noted that these operations can be combined, and individual queries may need parentheses when using additional clauses like LIMIT.