<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="set-operations">
      </a>
      15.2.14 Set Operations with UNION, INTERSECT, and EXCEPT
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045180321968">
  </a>
  <a class="indexterm" name="idm46045180320544">
  </a>
  <a class="indexterm" name="idm46045180319056">
  </a>
  <a class="indexterm" name="idm46045180317568">
  </a>
  <a class="indexterm" name="idm46045180316080">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="set-operations.html#set-operations-result-set-columns" title="Result Set Column Names and Data Types">
       Result Set Column Names and Data Types
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-operations.html#set-operations-table-values" title="Set Operations with TABLE and VALUES Statements">
       Set Operations with TABLE and VALUES Statements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-operations.html#set-operations-distinct-all" title="Set Operations using DISTINCT and ALL">
       Set Operations using DISTINCT and ALL
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-operations.html#set-operations-order-by-limit" title="Set Operations with ORDER BY and LIMIT">
       Set Operations with ORDER BY and LIMIT
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-operations.html#set-operations-limitations" title="Limitations of Set Operations">
       Limitations of Set Operations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   SQL set operations combine the results of multiple query blocks
      into a single result. A
   <span class="emphasis">
    <em>
     query block
    </em>
   </span>
   ,
      sometimes also known as a
   <span class="emphasis">
    <em>
     simple table
    </em>
   </span>
   , is
      any SQL statement that returns a result set, such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   . MySQL 8.4 also
      supports
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   and
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES
    </code>
   </a>
   statements. See the
      individual descriptions of these statements elsewhere in this
      chapter for additional information.
  </p>
  <p>
   The SQL standard defines the following three set operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      : Combine all results from
          two query blocks into a single result, omitting any
          duplicates.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="intersect.html" title="15.2.8 INTERSECT Clause">
       <code class="literal">
        INTERSECT
       </code>
      </a>
      : Combine only those
          rows which the results of two query blocks have in common,
          omitting any duplicates.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="except.html" title="15.2.4 EXCEPT Clause">
       <code class="literal">
        EXCEPT
       </code>
      </a>
      : For two query blocks
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      ,
          return all results from
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      which are
          not also present in
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      , omitting any
          duplicates.
     </p>
     <p>
      (Some database systems, such as Oracle, use
      <code class="literal">
       MINUS
      </code>
      for the name of this operator. This
          is not supported in MySQL.)
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL supports
   <code class="literal">
    UNION
   </code>
   ,
   <code class="literal">
    INTERSECT
   </code>
   , and
   <code class="literal">
    EXCEPT
   </code>
   .
  </p>
  <p>
   Each of these set operators supports an
   <code class="literal">
    ALL
   </code>
   modifier. When the
   <code class="literal">
    ALL
   </code>
   keyword follows a set
      operator, this causes duplicates to be included in the result. See
      the following sections covering the individual operators for more
      information and examples.
  </p>
  <p>
   All three set operators also support a
   <code class="literal">
    DISTINCT
   </code>
   keyword, which suppresses duplicates in the result. Since this is
      the default behavior for set operators, it is usually not
      necessary to specify
   <code class="literal">
    DISTINCT
   </code>
   explicitly.
  </p>
  <p>
   In general, query blocks and set operations can be combined in any
      number and order. A greatly simplified representation is shown
      here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8004692"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">query_block</em> <span class="token punctuation">[</span><em class="replaceable">set_op</em> <em class="replaceable">query_block</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">set_op</em> <em class="replaceable">query_block</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">query_block</em>:
    <span class="token keyword">SELECT</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token operator">|</span> <span class="token keyword">VALUES</span>

<em class="replaceable">set_op</em>:
    <span class="token keyword">UNION</span> <span class="token operator">|</span> <span class="token keyword">INTERSECT</span> <span class="token operator">|</span> <span class="token keyword">EXCEPT</span></code></pre>
  </div>
  <p>
   This can be represented more accurately, and in greater detail,
      like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94868057"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">query_expression</em>:
  <span class="token punctuation">[</span><em class="replaceable">with_clause</em><span class="token punctuation">]</span> <span class="token comment" spellcheck="true">/* WITH clause */</span> 
  <em class="replaceable">query_expression_body</em>
  <span class="token punctuation">[</span><em class="replaceable">order_by_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">limit_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">into_clause</em><span class="token punctuation">]</span>

<em class="replaceable">query_expression_body</em>:
    <em class="replaceable">query_term</em>
 <span class="token operator">|</span>  <em class="replaceable">query_expression_body</em> <span class="token keyword">UNION</span> <span class="token punctuation">[</span><span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <em class="replaceable">query_term</em>
 <span class="token operator">|</span>  <em class="replaceable">query_expression_body</em> <span class="token keyword">EXCEPT</span> <span class="token punctuation">[</span><span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <em class="replaceable">query_term</em>

<em class="replaceable">query_term</em>:
    <em class="replaceable">query_primary</em>
 <span class="token operator">|</span>  <em class="replaceable">query_term</em> <span class="token keyword">INTERSECT</span> <span class="token punctuation">[</span><span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">DISTINCT</span><span class="token punctuation">]</span> <em class="replaceable">query_primary</em>

<em class="replaceable">query_primary</em>:
    <em class="replaceable">query_block</em>
 <span class="token operator">|</span>  <span class="token string">'('</span> <em class="replaceable">query_expression_body</em> <span class="token punctuation">[</span><em class="replaceable">order_by_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">limit_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">into_clause</em><span class="token punctuation">]</span> <span class="token string">')'</span>

<em class="replaceable">query_block</em>:   <span class="token comment" spellcheck="true">/* also known as a simple table */</span>
    <em class="replaceable">query_specification</em>                     <span class="token comment" spellcheck="true">/* SELECT statement */</span>
 <span class="token operator">|</span>  <em class="replaceable">table_value_constructor</em>                 <span class="token comment" spellcheck="true">/* VALUES statement */</span>
 <span class="token operator">|</span>  <em class="replaceable">explicit_table</em>                          <span class="token comment" spellcheck="true">/* TABLE statement  */</span></code></pre>
  </div>
  <p>
   You should be aware that
   <code class="literal">
    INTERSECT
   </code>
   is evaluated
      before
   <code class="literal">
    UNION
   </code>
   or
   <code class="literal">
    EXCEPT
   </code>
   . This
      means that, for example,
   <code class="literal">
    TABLE x UNION TABLE y INTERSECT
      TABLE z
   </code>
   is always evaluated as
   <code class="literal">
    TABLE x UNION
      (TABLE y INTERSECT TABLE z)
   </code>
   . See
   <a class="xref" href="intersect.html" title="15.2.8 INTERSECT Clause">
    Section 15.2.8, “INTERSECT Clause”
   </a>
   , for more information.
  </p>
  <p>
   In addition, you should keep in mind that, while the
   <code class="literal">
    UNION
   </code>
   and
   <code class="literal">
    INTERSECT
   </code>
   set
      operators are commutative (ordering is not significant),
   <code class="literal">
    EXCEPT
   </code>
   is not (order of operands affects the
      outcome). In other words, all of the following statements are
      true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE x UNION TABLE y
      </code>
      and
      <code class="literal">
       TABLE y
          UNION TABLE x
      </code>
      produce the same result, although the
          ordering of the rows may differ. You can force them to be the
          same using
      <code class="literal">
       ORDER BY
      </code>
      ; see
      <a class="xref" href="set-operations.html#set-operations-order-by-limit" title="Set Operations with ORDER BY and LIMIT">
       Set Operations with ORDER BY and LIMIT
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE x INTERSECT TABLE y
      </code>
      and
      <code class="literal">
       TABLE y INTERSECT TABLE x
      </code>
      return the same
          result.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE x EXCEPT TABLE y
      </code>
      and
      <code class="literal">
       TABLE y
          EXCEPT TABLE x
      </code>
      do
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      yield the
          same result. See
      <a class="xref" href="except.html" title="15.2.4 EXCEPT Clause">
       Section 15.2.4, “EXCEPT Clause”
      </a>
      , for an example.
     </p>
    </li>
   </ul>
  </div>
  <p>
   More information and examples can be found in the sections that
      follow.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="set-operations-result-set-columns">
       </a>
       Result Set Column Names and Data Types
      </h4>
     </div>
    </div>
   </div>
   <p>
    The column names for the result of a set operation are taken
        from the column names of the first query block. Example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12046445"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>x <span class="token datatype">INT</span><span class="token punctuation">,</span> y <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.00 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.01 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> x    <span class="token punctuation">|</span> y    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>   <span class="token punctuation">-</span>2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>   <span class="token punctuation">-</span>2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    This is true for
    <code class="literal">
     UNION
    </code>
    ,
    <code class="literal">
     EXCEPT
    </code>
    , and
    <code class="literal">
     INTERSECT
    </code>
    queries.
   </p>
   <p>
    Selected columns listed in corresponding positions of each query
        block should have the same data type. For example, the first
        column selected by the first statement should have the same type
        as the first column selected by the other statements. If the
        data types of corresponding result columns do not match, the
        types and lengths of the columns in the result take into account
        the values retrieved by all of the query blocks. For example,
        the column length in the result set is not constrained to the
        length of the value from the first statement, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74849681"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">REPEAT</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token function">REPEAT</span><span class="token punctuation">(</span><span class="token string">'b'</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> REPEAT('a',1)        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a                    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> bbbbbbbbbbbbbbbbbbbb <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="set-operations-table-values">
       </a>
       Set Operations with TABLE and VALUES Statements
      </h4>
     </div>
    </div>
   </div>
   <p>
    You can also use a
    <a class="link" href="table.html" title="15.2.16 TABLE Statement">
     <code class="literal">
      TABLE
     </code>
    </a>
    statement or
    <a class="link" href="values.html" title="15.2.19 VALUES Statement">
     <code class="literal">
      VALUES
     </code>
    </a>
    statement
        wherever you can employ the equivalent
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement. Assume that
        tables
    <code class="literal">
     t1
    </code>
    and
    <code class="literal">
     t2
    </code>
    are
        created and populated as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88529161"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>x <span class="token datatype">INT</span><span class="token punctuation">,</span> y <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The preceding being the case, and disregarding the column names
        in the output of the queries beginning with
    <a class="link" href="values.html" title="15.2.19 VALUES Statement">
     <code class="literal">
      VALUES
     </code>
    </a>
    , all of the following
    <code class="literal">
     UNION
    </code>
    queries yield the same result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa668531"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token keyword">TABLE</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t2<span class="token punctuation">;</span>
<span class="token keyword">TABLE</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t2<span class="token punctuation">;</span>
<span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">TABLE</span> t1 <span class="token keyword">UNION</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To force the column names to be the same, wrap the query block
        on the left-hand side in a
    <code class="literal">
     SELECT
    </code>
    statement,
        and use aliases, like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9271812"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">TABLE</span> t2<span class="token punctuation">)</span> <span class="token keyword">AS</span> t<span class="token punctuation">(</span>x<span class="token punctuation">,</span>y<span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> x    <span class="token punctuation">|</span> y    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>   <span class="token punctuation">-</span>2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="set-operations-distinct-all">
       </a>
       Set Operations using DISTINCT and ALL
      </h4>
     </div>
    </div>
   </div>
   <p>
    By default, duplicate rows are removed from results of set
        operations. The optional
    <code class="literal">
     DISTINCT
    </code>
    keyword has
        the same effect but makes it explicit. With the optional
    <code class="literal">
     ALL
    </code>
    keyword, duplicate-row removal does not
        occur and the result includes all matching rows from all queries
        in the union.
   </p>
   <p>
    You can mix
    <code class="literal">
     ALL
    </code>
    and
    <code class="literal">
     DISTINCT
    </code>
    in the same query. Mixed types are
        treated such that a set operation using
    <code class="literal">
     DISTINCT
    </code>
    overrides any such operation using
    <code class="literal">
     ALL
    </code>
    to its left. A
    <code class="literal">
     DISTINCT
    </code>
    set can be produced explicitly by
        using
    <code class="literal">
     DISTINCT
    </code>
    with
    <a class="link" href="union.html" title="15.2.18 UNION Clause">
     <code class="literal">
      UNION
     </code>
    </a>
    ,
    <a class="link" href="intersect.html" title="15.2.8 INTERSECT Clause">
     <code class="literal">
      INTERSECT
     </code>
    </a>
    , or
    <a class="link" href="except.html" title="15.2.4 EXCEPT Clause">
     <code class="literal">
      EXCEPT
     </code>
    </a>
    , or implicitly by using
        the set operations with no following
    <code class="literal">
     DISTINCT
    </code>
    or
    <code class="literal">
     ALL
    </code>
    keyword.
   </p>
   <p>
    Set operations work the same way when one or more
    <a class="link" href="table.html" title="15.2.16 TABLE Statement">
     <code class="literal">
      TABLE
     </code>
    </a>
    statements,
    <a class="link" href="values.html" title="15.2.19 VALUES Statement">
     <code class="literal">
      VALUES
     </code>
    </a>
    statements, or both, are
        used to generate the set.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="set-operations-order-by-limit">
       </a>
       Set Operations with ORDER BY and LIMIT
      </h4>
     </div>
    </div>
   </div>
   <p>
    To apply an
    <code class="literal">
     ORDER BY
    </code>
    or
    <code class="literal">
     LIMIT
    </code>
    clause to an individual query block
        used as part of a union, intersection, or other set operation,
        parenthesize the query block, placing the clause inside the
        parentheses, like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88942099"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">10</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">)</span>
<span class="token keyword">UNION</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">11</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">2</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token punctuation">(</span><span class="token keyword">TABLE</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> x <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">)</span> 
<span class="token keyword">INTERSECT</span> 
<span class="token punctuation">(</span><span class="token keyword">TABLE</span> t2 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Use of
    <code class="literal">
     ORDER BY
    </code>
    for individual query blocks
        or statements implies nothing about the order in which the rows
        appear in the final result because the rows produced by a set
        operation are by default unordered. Therefore,
    <code class="literal">
     ORDER
        BY
    </code>
    in this context typically is used in conjunction
        with
    <code class="literal">
     LIMIT
    </code>
    , to determine the subset of the
        selected rows to retrieve, even though it does not necessarily
        affect the order of those rows in the final result. If
    <code class="literal">
     ORDER BY
    </code>
    appears without
    <code class="literal">
     LIMIT
    </code>
    within a query block, it is optimized
        away because it has no effect in any case.
   </p>
   <p>
    To use an
    <code class="literal">
     ORDER BY
    </code>
    or
    <code class="literal">
     LIMIT
    </code>
    clause to sort or limit the entire
        result of a set operation, place the
    <code class="literal">
     ORDER BY
    </code>
    or
    <code class="literal">
     LIMIT
    </code>
    after the last statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92958130"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t1
<span class="token keyword">EXCEPT</span>
<span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">11</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">2</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>

<span class="token keyword">TABLE</span> t1
<span class="token keyword">UNION</span> 
<span class="token keyword">TABLE</span> t2
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If one or more individual statements make use of
    <code class="literal">
     ORDER
        BY
    </code>
    ,
    <code class="literal">
     LIMIT
    </code>
    , or both, and, in
        addition, you wish to apply an ORDER BY, LIMIT, or both to the
        entire result, then each such individual statement must be
        enclosed in parentheses.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91063797"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">10</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token keyword">EXCEPT</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">11</span> <span class="token operator">AND</span> b<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>

<span class="token punctuation">(</span><span class="token keyword">TABLE</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">)</span> 
<span class="token keyword">UNION</span> 
<span class="token keyword">TABLE</span> t2 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A statement with no
    <code class="literal">
     ORDER BY
    </code>
    or
    <code class="literal">
     LIMIT
    </code>
    clause does need to be parenthesized;
        replacing
    <code class="literal">
     TABLE t2
    </code>
    with
    <code class="literal">
     (TABLE
        t2)
    </code>
    in the second statement of the two just shown does
        not alter the result of the
    <code class="literal">
     UNION
    </code>
    .
   </p>
   <p>
    You can also use
    <code class="literal">
     ORDER BY
    </code>
    and
    <code class="literal">
     LIMIT
    </code>
    with
    <a class="link" href="values.html" title="15.2.19 VALUES Statement">
     <code class="literal">
      VALUES
     </code>
    </a>
    statements in set
        operations, as shown in this example using the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    client:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78463302"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">UNION</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> column_0 <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">3</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> column_0 <span class="token punctuation">|</span> column_1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>        5 <span class="token punctuation">|</span>        9 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>        4 <span class="token punctuation">|</span>       <span class="token punctuation">-</span>2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    (You should keep in mind that neither
    <code class="literal">
     TABLE
    </code>
    statements nor
    <code class="literal">
     VALUES
    </code>
    statements accept a
    <code class="literal">
     WHERE
    </code>
    clause.)
   </p>
   <p>
    This kind of
    <code class="literal">
     ORDER BY
    </code>
    cannot use column
        references that include a table name (that is, names in
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    .
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    format). Instead, provide a column alias in the first query
        block, and refer to the alias in the
    <code class="literal">
     ORDER BY
    </code>
    clause. (You can also refer to the column in the
    <code class="literal">
     ORDER
        BY
    </code>
    clause using its column position, but such use of
        column positions is deprecated, and thus subject to eventual
        removal in a future MySQL release.)
   </p>
   <p>
    If a column to be sorted is aliased, the
    <code class="literal">
     ORDER
        BY
    </code>
    clause
    <span class="emphasis">
     <em>
      must
     </em>
    </span>
    refer to the
        alias, not the column name. The first of the following
        statements is permitted, but the second fails with an
    <code class="literal">
     Unknown column 'a' in 'order clause'
    </code>
    error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8811978"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">AS</span> b <span class="token keyword">FROM</span> t<span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> b<span class="token punctuation">;</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> a <span class="token keyword">AS</span> b <span class="token keyword">FROM</span> t<span class="token punctuation">)</span> <span class="token keyword">UNION</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To cause rows in a
    <a class="link" href="union.html" title="15.2.18 UNION Clause">
     <code class="literal">
      UNION
     </code>
    </a>
    result
        to consist of the sets of rows retrieved by each query block one
        after the other, select an additional column in each query block
        to use as a sort column and add an
    <code class="literal">
     ORDER BY
    </code>
    clause that sorts on that column following the last query block:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82061125"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> sort_col<span class="token punctuation">,</span> col1a<span class="token punctuation">,</span> col1b<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span>
<span class="token keyword">UNION</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">2</span><span class="token punctuation">,</span> col2a<span class="token punctuation">,</span> col2b<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> sort_col<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To maintain sort order within individual results, add a
        secondary column to the
    <code class="literal">
     ORDER BY
    </code>
    clause:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4349862"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> sort_col<span class="token punctuation">,</span> col1a<span class="token punctuation">,</span> col1b<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span>
<span class="token keyword">UNION</span>
<span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">2</span><span class="token punctuation">,</span> col2a<span class="token punctuation">,</span> col2b<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> sort_col<span class="token punctuation">,</span> col1a<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Use of an additional column also enables you to determine which
        query block each row comes from. Extra columns can provide other
        identifying information as well, such as a string that indicates
        a table name.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="set-operations-limitations">
       </a>
       Limitations of Set Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    Set operations in MySQL are subject to some limitations, which
        are described in the next few paragraphs.
   </p>
   <p>
    Set operations including
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements have the following limitations:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        HIGH_PRIORITY
       </code>
       in the first
       <code class="literal">
        SELECT
       </code>
       has no effect.
       <code class="literal">
        HIGH_PRIORITY
       </code>
       in any subsequent
       <code class="literal">
        SELECT
       </code>
       produces a syntax error.
      </p>
     </li>
     <li class="listitem">
      <p>
       Only the last
       <code class="literal">
        SELECT
       </code>
       statement can use an
       <code class="literal">
        INTO
       </code>
       clause. However, the entire
       <code class="literal">
        UNION
       </code>
       result is written to the
       <code class="literal">
        INTO
       </code>
       output destination.
      </p>
     </li>
    </ul>
   </div>
   <p>
    These two
    <code class="literal">
     UNION
    </code>
    variants containing
    <code class="literal">
     INTO
    </code>
    are deprecated; you should expect
        support for them to be removed in a future version of MySQL:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       In the trailing query block of a query expression, use of
       <code class="literal">
        INTO
       </code>
       before
       <code class="literal">
        FROM
       </code>
       produces a warning. Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa82940770"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">INTO</span> <span class="token keyword">OUTFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span> <span class="token keyword">FROM</span> <span class="token keyword"><em class="replaceable">table_name</em></span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In a parenthesized trailing block of a query expression, use
            of
       <code class="literal">
        INTO
       </code>
       (regardless of its position
            relative to
       <code class="literal">
        FROM
       </code>
       ) produces a warning.
            Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91159609"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">UNION</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">INTO</span> <span class="token keyword">OUTFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span> <span class="token keyword">FROM</span> <span class="token keyword"><em class="replaceable">table_name</em></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Those variants are deprecated because they are confusing, as
            if they collect information from the named table rather than
            the entire query expression (the
       <code class="literal">
        UNION
       </code>
       ).
      </p>
     </li>
    </ul>
   </div>
   <p>
    Set operations with an aggregate function in an
    <code class="literal">
     ORDER
        BY
    </code>
    clause are rejected with
    <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_aggregate_order_for_union" target="_top">
     <code class="literal">
      ER_AGGREGATE_ORDER_FOR_UNION
     </code>
    </a>
    .
        Although the error name might suggest that this is exclusive to
    <code class="literal">
     UNION
    </code>
    queries, the preceding is also true for
    <code class="literal">
     EXCEPT
    </code>
    and
    <code class="literal">
     INTERSECT
    </code>
    queries, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7799723"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">INTERSECT</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">MAX</span><span class="token punctuation">(</span>x<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3028 (HY000)<span class="token punctuation">:</span> Expression #1 of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT</span></code></pre>
   </div>
   <p>
    A locking clause (such as
    <code class="literal">
     FOR UPDATE
    </code>
    or
    <code class="literal">
     LOCK IN SHARE MODE
    </code>
    ) applies to the query
        block it follows. This means that, in a
    <code class="literal">
     SELECT
    </code>
    statement used with set operations, a
        locking clause can be used only if the query block and locking
        clause are enclosed in parentheses.
   </p>
  </div>
 </div>
 <br/>
</div>
