<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-selection">
      </a>
      26.5 Partition Selection
     </h2>
    </div>
   </div>
  </div>
  <p>
   Explicit selection of partitions and subpartitions for rows
      matching a given
   <code class="literal">
    WHERE
   </code>
   condition is supported.
      Partition selection is similar to partition pruning, in that only
      specific partitions are checked for matches, but differs in two
      key respects:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      The partitions to be checked are specified by the issuer of
          the statement, unlike partition pruning, which is automatic.
     </p>
    </li>
    <li class="listitem">
     <p>
      Whereas partition pruning applies only to queries, explicit
          selection of partitions is supported for both queries and a
          number of DML statements.
     </p>
    </li>
   </ol>
  </div>
  <p>
   SQL statements supporting explicit partition selection are listed
      here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
       <code class="literal">
        LOAD XML
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The remainder of this section discusses explicit partition
      selection as it applies generally to the statements just listed,
      and provides some examples.
  </p>
  <p>
   Explicit partition selection is implemented using a
   <code class="literal">
    PARTITION
   </code>
   option. For all supported statements,
      this option uses the syntax shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25190233"><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">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_names</em><span class="token punctuation">)</span>

      <em class="replaceable">partition_names</em>:
          <em class="replaceable">partition_name</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   This option always follows the name of the table to which the
      partition or partitions belong.
   <em class="replaceable">
    <code>
     partition_names
    </code>
   </em>
   is a comma-separated
      list of partitions or subpartitions to be used. Each name in this
      list must be the name of an existing partition or subpartition of
      the specified table; if any of the partitions or subpartitions are
      not found, the statement fails with an error (
   <span class="errortext">
    partition
      '
    <em class="replaceable">
     <code>
      partition_name
     </code>
    </em>
    ' doesn't
      exist
   </span>
   ). Partitions and subpartitions named in
   <em class="replaceable">
    <code>
     partition_names
    </code>
   </em>
   may be listed in any
      order, and may overlap.
  </p>
  <p>
   When the
   <code class="literal">
    PARTITION
   </code>
   option is used, only the
      partitions and subpartitions listed are checked for matching rows.
      This option can be used in a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement to determine which rows belong to a given partition.
      Consider a partitioned table named
   <code class="literal">
    employees
   </code>
   ,
      created and populated using the statements shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14538669"><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">SET</span> <span class="token variable">@@SQL_MODE</span> <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> employees  <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    department_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>  <span class="token punctuation">(</span>
        <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Bob'</span><span class="token punctuation">,</span> <span class="token string">'Taylor'</span><span class="token punctuation">,</span> <span class="token number">3</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Frank'</span><span class="token punctuation">,</span> <span class="token string">'Williams'</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Ellen'</span><span class="token punctuation">,</span> <span class="token string">'Johnson'</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Jim'</span><span class="token punctuation">,</span> <span class="token string">'Smith'</span><span class="token punctuation">,</span> <span class="token number">2</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Mary'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Linda'</span><span class="token punctuation">,</span> <span class="token string">'Black'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Ed'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'June'</span><span class="token punctuation">,</span> <span class="token string">'Wilson'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Andy'</span><span class="token punctuation">,</span> <span class="token string">'Smith'</span><span class="token punctuation">,</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 punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Lou'</span><span class="token punctuation">,</span> <span class="token string">'Waters'</span><span class="token punctuation">,</span> <span class="token number">2</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Jill'</span><span class="token punctuation">,</span> <span class="token string">'Stone'</span><span class="token punctuation">,</span> <span class="token number">1</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Roger'</span><span class="token punctuation">,</span> <span class="token string">'White'</span><span class="token punctuation">,</span> <span class="token number">3</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Howard'</span><span class="token punctuation">,</span> <span class="token string">'Andrews'</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Fred'</span><span class="token punctuation">,</span> <span class="token string">'Goldberg'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Barbara'</span><span class="token punctuation">,</span> <span class="token string">'Brown'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Alice'</span><span class="token punctuation">,</span> <span class="token string">'Rogers'</span><span class="token punctuation">,</span> <span class="token number">2</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 punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Mark'</span><span class="token punctuation">,</span> <span class="token string">'Morgan'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Karen'</span><span class="token punctuation">,</span> <span class="token string">'Cole'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can see which rows are stored in partition
   <code class="literal">
    p1
   </code>
   like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37629450"><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> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p1<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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname  <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 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> Mary  <span class="token punctuation">|</span> Jones  <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>             1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span> Linda <span class="token punctuation">|</span> Black  <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>             3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  7 <span class="token punctuation">|</span> Ed    <span class="token punctuation">|</span> Jones  <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>             1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  8 <span class="token punctuation">|</span> June  <span class="token punctuation">|</span> Wilson <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>             1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  9 <span class="token punctuation">|</span> Andy  <span class="token punctuation">|</span> Smith  <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>             3 <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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">5 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The result is the same as obtained by the query
   <code class="literal">
    SELECT *
      FROM employees WHERE id BETWEEN 5 AND 9
   </code>
   .
  </p>
  <p>
   To obtain rows from multiple partitions, supply their names as a
      comma-delimited list. For example,
   <code class="literal">
    SELECT * FROM
      employees PARTITION (p1, p2)
   </code>
   returns all rows from
      partitions
   <code class="literal">
    p1
   </code>
   and
   <code class="literal">
    p2
   </code>
   while
      excluding rows from the remaining partitions.
  </p>
  <p>
   Any valid query against a partitioned table can be rewritten with
      a
   <code class="literal">
    PARTITION
   </code>
   option to restrict the result to
      one or more desired partitions. You can use
   <code class="literal">
    WHERE
   </code>
   conditions,
   <code class="literal">
    ORDER BY
   </code>
   and
   <code class="literal">
    LIMIT
   </code>
   options, and so on. You can also use
      aggregate functions with
   <code class="literal">
    HAVING
   </code>
   and
   <code class="literal">
    GROUP BY
   </code>
   options. Each of the following queries
      produces a valid result when run on the
   <code class="literal">
    employees
   </code>
   table as previously defined:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24963182"><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> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">,</span> p2<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> lname <span class="token operator">LIKE</span> <span class="token string">'S%'</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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span> Jim   <span class="token punctuation">|</span> Smith <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>             4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill  <span class="token punctuation">|</span> Stone <span class="token punctuation">|</span>        1 <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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>fname<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> lname<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">name</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> lname<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> id <span class="token punctuation">|</span> name           <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>  3 <span class="token punctuation">|</span> Ellen Johnson  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span> Jim Smith      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> Bob Taylor     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span> Frank Williams <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">4 rows in set (0.06 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> store_id<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>department_id<span class="token punctuation">)</span> <span class="token keyword">AS</span> c
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p1<span class="token punctuation">,</span>p2<span class="token punctuation">,</span>p3<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> store_id <span class="token keyword">HAVING</span> c <span class="token operator">&gt;</span> <span class="token number">4</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>
<span class="token output"><span class="token punctuation">|</span> c <span class="token punctuation">|</span> store_id <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>
<span class="token output"><span class="token punctuation">|</span> 5 <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>        3 <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>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Statements using partition selection can be employed with tables
      using any of the supported partitioning types. When a table is
      created using
   <code class="literal">
    [LINEAR] HASH
   </code>
   or
   <code class="literal">
    [LINEAR] KEY
   </code>
   partitioning and the names of the
      partitions are not specified, MySQL automatically names the
      partitions
   <code class="literal">
    p0
   </code>
   ,
   <code class="literal">
    p1
   </code>
   ,
   <code class="literal">
    p2
   </code>
   , ...,
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      N-1
     </code>
    </em>
   </code>
   , where
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   is the number of partitions. For
      subpartitions not explicitly named, MySQL assigns automatically to
      the subpartitions in each partition
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
   </code>
   the names
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    sp0
   </code>
   ,
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    sp1
   </code>
   ,
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    sp2
   </code>
   , ...,
   <code class="literal">
    p
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    sp
    <em class="replaceable">
     <code>
      M-1
     </code>
    </em>
   </code>
   ,
      where
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   is the number of subpartitions.
      When executing against this table a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   (or other SQL statement for
      which explicit partition selection is allowed), you can use these
      generated names in a
   <code class="literal">
    PARTITION
   </code>
   option, as shown
      here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67093205"><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> employees_sub  <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     store_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     department_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> pk <span class="token punctuation">(</span>id<span class="token punctuation">,</span> lname<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>lname<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SUBPARTITIONS</span> <span class="token number">2</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.14 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees_sub   <span class="token comment" spellcheck="true"># reuse data in employees table</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
<span class="token output">Query OK, 18 rows affected (0.09 sec)</span>
<span class="token output">Records: 18  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>fname<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> lname<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">name</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> employees_sub <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p2sp1<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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> name          <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>
<span class="token output"><span class="token punctuation">|</span> 10 <span class="token punctuation">|</span> Lou Waters    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 14 <span class="token punctuation">|</span> Fred Goldberg <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>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   You may also use a
   <code class="literal">
    PARTITION
   </code>
   option in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   portion of an
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
      SELECT
    </code>
   </a>
   statement, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13454971"><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> employees_copy <span class="token operator">LIKE</span> employees<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.28 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees_copy
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p2<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 5 rows affected (0.04 sec)</span>
<span class="token output">Records: 5  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees_copy<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 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 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> id <span class="token punctuation">|</span> fname  <span class="token punctuation">|</span> lname    <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 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> 10 <span class="token punctuation">|</span> Lou    <span class="token punctuation">|</span> Waters   <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>             4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill   <span class="token punctuation">|</span> Stone    <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>             4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 12 <span class="token punctuation">|</span> Roger  <span class="token punctuation">|</span> White    <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>             2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 13 <span class="token punctuation">|</span> Howard <span class="token punctuation">|</span> Andrews  <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> 14 <span class="token punctuation">|</span> Fred   <span class="token punctuation">|</span> Goldberg <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>             3 <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 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 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">5 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Partition selection can also be used with joins. Suppose we create
      and populate two tables using the statements shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81897609"><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> stores <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    city <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> stores <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Nambucca'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Uranga'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Bellingen'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Grafton'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> departments  <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Sales'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Customer Service'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Delivery'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">,</span> <span class="token string">'Accounting'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can explicitly select partitions (or subpartitions, or both)
      from any or all of the tables in a join. (The
   <code class="literal">
    PARTITION
   </code>
   option used to select partitions from
      a given table immediately follows the name of the table, before
      all other options, including any table alias.) For example, the
      following query gets the name, employee ID, department, and city
      of all employees who work in the Sales or Delivery department
      (partition
   <code class="literal">
    p1
   </code>
   of the
   <code class="literal">
    departments
   </code>
   table) at the stores in either of
      the cities of Nambucca and Bellingen (partition
   <code class="literal">
    p0
   </code>
   of the
   <code class="literal">
    stores
   </code>
   table):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55853254"><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 prompt">    -&gt;</span>     e<span class="token punctuation">.</span>id <span class="token keyword">AS</span> <span class="token string">'Employee ID'</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>e<span class="token punctuation">.</span>fname<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> e<span class="token punctuation">.</span>lname<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">Name</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     s<span class="token punctuation">.</span>city <span class="token keyword">AS</span> City<span class="token punctuation">,</span> d<span class="token punctuation">.</span><span class="token keyword">name</span> <span class="token keyword">AS</span> department
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span> employees <span class="token keyword">AS</span> e
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> stores <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p1<span class="token punctuation">)</span> <span class="token keyword">AS</span> s <span class="token keyword">ON</span> e<span class="token punctuation">.</span>store_id<span class="token operator">=</span>s<span class="token punctuation">.</span>id
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> departments <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">)</span> <span class="token keyword">AS</span> d <span class="token keyword">ON</span> e<span class="token punctuation">.</span>department_id<span class="token operator">=</span>d<span class="token punctuation">.</span>id
<span class="token prompt">    -&gt;</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> e<span class="token punctuation">.</span>lname<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 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 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> Employee ID <span class="token punctuation">|</span> Name          <span class="token punctuation">|</span> City      <span class="token punctuation">|</span> department <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 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 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>          14 <span class="token punctuation">|</span> Fred Goldberg <span class="token punctuation">|</span> Bellingen <span class="token punctuation">|</span> Delivery   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>           5 <span class="token punctuation">|</span> Mary Jones    <span class="token punctuation">|</span> Nambucca  <span class="token punctuation">|</span> Sales      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>          17 <span class="token punctuation">|</span> Mark Morgan   <span class="token punctuation">|</span> Bellingen <span class="token punctuation">|</span> Delivery   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>           9 <span class="token punctuation">|</span> Andy Smith    <span class="token punctuation">|</span> Nambucca  <span class="token punctuation">|</span> Delivery   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>           8 <span class="token punctuation">|</span> June Wilson   <span class="token punctuation">|</span> Bellingen <span class="token punctuation">|</span> Sales      <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 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 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">5 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   For general information about joins in MySQL, see
   <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
    Section 15.2.13.2, “JOIN Clause”
   </a>
   .
  </p>
  <p>
   When the
   <code class="literal">
    PARTITION
   </code>
   option is used with
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statements, only those
      partitions (and subpartitions, if any) listed with the option are
      checked for rows to be deleted. Any other partitions are ignored,
      as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49782207"><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> employees <span class="token keyword">WHERE</span> fname <span class="token operator">LIKE</span> <span class="token string">'j%'</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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname  <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 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> Jim   <span class="token punctuation">|</span> Smith  <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>             4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  8 <span class="token punctuation">|</span> June  <span class="token punctuation">|</span> Wilson <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>             1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill  <span class="token punctuation">|</span> Stone  <span class="token punctuation">|</span>        1 <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 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 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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">,</span> p1<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> fname <span class="token operator">LIKE</span> <span class="token string">'j%'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.09 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> fname <span class="token operator">LIKE</span> <span class="token string">'j%'</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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill  <span class="token punctuation">|</span> Stone <span class="token punctuation">|</span>        1 <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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Only the two rows in partitions
   <code class="literal">
    p0
   </code>
   and
   <code class="literal">
    p1
   </code>
   matching the
   <code class="literal">
    WHERE
   </code>
   condition were deleted. As you can see from the result when the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   is run a second time, there
      remains a row in the table matching the
   <code class="literal">
    WHERE
   </code>
   condition, but residing in a different partition
      (
   <code class="literal">
    p2
   </code>
   ).
  </p>
  <p>
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements using explicit
      partition selection behave in the same way; only rows in the
      partitions referenced by the
   <code class="literal">
    PARTITION
   </code>
   option
      are considered when determining the rows to be updated, as can be
      seen by executing the following statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17747091"><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">UPDATE</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SET</span> store_id <span class="token operator">=</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> fname <span class="token operator">=</span> <span class="token string">'Jill'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token output">Rows matched: 0  Changed: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> fname <span class="token operator">=</span> <span class="token string">'Jill'</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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill  <span class="token punctuation">|</span> Stone <span class="token punctuation">|</span>        1 <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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p2<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SET</span> store_id <span class="token operator">=</span> <span class="token number">2</span> <span class="token keyword">WHERE</span> fname <span class="token operator">=</span> <span class="token string">'Jill'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.09 sec)</span>
<span class="token output">Rows matched: 1  Changed: 1  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> fname <span class="token operator">=</span> <span class="token string">'Jill'</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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span> store_id <span class="token punctuation">|</span> department_id <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 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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span> Jill  <span class="token punctuation">|</span> Stone <span class="token punctuation">|</span>        2 <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 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   In the same way, when
   <code class="literal">
    PARTITION
   </code>
   is used with
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   , only rows in the partition
      or partitions named in the partition list are checked for
      deletion.
  </p>
  <p>
   For statements that insert rows, the behavior differs in that
      failure to find a suitable partition causes the statement to fail.
      This is true for both
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   and
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   statements, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11020998"><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">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p2<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'Jan'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</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 punctuation">;</span>
<span class="token output">ERROR 1729 (HY000)<span class="token punctuation">:</span> Found a row not matching the given partition set</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p3<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'Jan'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</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 punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.07 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">REPLACE</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'Jan'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</span> <span class="token number">3</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 output">ERROR 1729 (HY000)<span class="token punctuation">:</span> Found a row not matching the given partition set
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">REPLACE</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p3<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'Jan'</span><span class="token punctuation">,</span> <span class="token string">'Jones'</span><span class="token punctuation">,</span> <span class="token number">3</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 output">Query OK, 2 rows affected (0.09 sec)</span></code></pre>
  </div>
  <p>
   For statements that write multiple rows to a partitioned table
      that using the
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   storage engine:
      If any row in the list following
   <code class="literal">
    VALUES
   </code>
   cannot
      be written to one of the partitions specified in the
   <em class="replaceable">
    <code>
     partition_names
    </code>
   </em>
   list, the entire
      statement fails and no rows are written. This is shown for
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements in the following
      example, reusing the
   <code class="literal">
    employees
   </code>
   table created
      previously:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43978087"><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">ALTER</span> <span class="token keyword">TABLE</span> employees
<span class="token prompt">    -&gt;</span>     <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> p3 <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</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 prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p5 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 6 rows affected (2.09 sec)</span>
<span class="token output">Records: 6  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> employees\G
<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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <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 class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> employees
Create Table<span class="token punctuation">:</span> CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  `store_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/<span class="token punctuation">*</span>!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) <span class="token punctuation">*</span>/
</span><span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p3<span class="token punctuation">,</span> p4<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">24</span><span class="token punctuation">,</span> <span class="token string">'Tim'</span><span class="token punctuation">,</span> <span class="token string">'Greene'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>  <span class="token punctuation">(</span><span class="token number">26</span><span class="token punctuation">,</span> <span class="token string">'Linda'</span><span class="token punctuation">,</span> <span class="token string">'Mills'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1729 (HY000)<span class="token punctuation">:</span> Found a row not matching the given partition set
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p3<span class="token punctuation">,</span> p4<span class="token punctuation">,</span> p5<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">24</span><span class="token punctuation">,</span> <span class="token string">'Tim'</span><span class="token punctuation">,</span> <span class="token string">'Greene'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>  <span class="token punctuation">(</span><span class="token number">26</span><span class="token punctuation">,</span> <span class="token string">'Linda'</span><span class="token punctuation">,</span> <span class="token string">'Mills'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.06 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   The preceding is true for both
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements and
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   statements that write
      multiple rows.
  </p>
  <p>
   Partition selection is disabled for tables employing a storage
      engine that supplies automatic partitioning, such as
   <code class="literal">
    NDB
   </code>
   .
  </p>
 </div>
 <br/>
</div>
