<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="hash-joins">
      </a>
      10.2.1.4 Hash Join Optimization
     </h4>
    </div>
   </div>
  </div>
  <p>
   By default, MySQL employs hash joins whenever possible. It is
          possible to control whether hash joins are employed using one
          of the
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     BNL
    </code>
   </a>
   and
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     NO_BNL
    </code>
   </a>
   optimizer hints, or
          by setting
   <a class="link" href="switchable-optimizations.html#optflag_block-nested-loop">
    <code class="literal">
     block_nested_loop=on
    </code>
   </a>
   or
   <code class="literal">
    block_nested_loop=off
   </code>
   as part of the
          setting for the optimizer_switch server system variable.
  </p>
  <p>
   MySQL employs a hash join for any query for which each join
          has an equi-join condition, and in which there are no indexes
          that can be applied to any join conditions, such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95488814"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
    <span class="token keyword">FROM</span> t1
    <span class="token keyword">JOIN</span> t2
        <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>c1<span class="token operator">=</span>t2<span class="token punctuation">.</span>c1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A hash join can also be used when there are one or more
          indexes that can be used for single-table predicates.
  </p>
  <p>
   In the example just shown and the remaining examples in this
          section, we assume that the three tables
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    t3
   </code>
   have been created using the following
          statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16835890"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can see that a hash join is being employed by using
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   ,
          like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66334182"><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">EXPLAIN</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>c1<span class="token operator">=</span>t2<span class="token punctuation">.</span>c1\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>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t1
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> NULL
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. 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>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t2
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using where; Using join buffer (hash join)</span></code></pre>
  </div>
  <p>
   <a class="link" href="explain.html#explain-analyze" title="Obtaining Information with EXPLAIN ANALYZE">
    <code class="literal">
     EXPLAIN ANALYZE
    </code>
   </a>
   also displays
          information about hash joins used.
  </p>
  <p>
   The hash join is used for queries involving multiple joins as
          well, as long as at least one join condition for each pair of
          tables is an equi-join, like the query shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63933684"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
    <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1 <span class="token operator">AND</span> t1<span class="token punctuation">.</span>c2 <span class="token operator">&lt;</span> t2<span class="token punctuation">.</span>c2<span class="token punctuation">)</span>
    <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t3<span class="token punctuation">.</span>c1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In cases like the one just shown, which makes use of an inner
          join, any extra conditions which are not equi-joins are
          applied as filters after the join is executed. (For outer
          joins, such as left joins, semijoins, and antijoins, they are
          printed as part of the join.) This can be seen here in the
          output of
   <code class="literal">
    EXPLAIN
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67070766"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t2
<span class="token prompt">    -&gt;</span>         <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1 <span class="token operator">AND</span> t1<span class="token punctuation">.</span>c2 <span class="token operator">&lt;</span> t2<span class="token punctuation">.</span>c2<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t3
<span class="token prompt">    -&gt;</span>         <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t3<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t3  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Filter</span>: <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>c2 <span class="token operator">&lt;</span> t2<span class="token punctuation">.</span>c2<span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.70</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">            -&gt;</span> <span class="token keyword">Inner</span> <span class="token keyword">hash</span> <span class="token keyword">join</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t1<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.70</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">                -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">                -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">                    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   As also can be seen from the output just shown, multiple hash
          joins can be (and are) used for joins having multiple
          equi-join conditions.
  </p>
  <p>
   A hash join is used even if any pair of joined tables does not
          have at least one equi-join condition, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85718368"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>c1 <span class="token operator">&lt;</span> t3<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Filter<span class="token punctuation">:</span> (t1.c1 &lt; t3.c1)  (cost=1.05 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Inner</span> <span class="token keyword">hash</span> <span class="token keyword">join</span> <span class="token punctuation">(</span><span class="token keyword">no</span> <span class="token keyword">condition</span><span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">1.05</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t3  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">            -&gt;</span> <span class="token keyword">Inner</span> <span class="token keyword">hash</span> <span class="token keyword">join</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t1<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.70</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">                -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">                -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">                    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   (Additional examples are provided later in this section.)
  </p>
  <p>
   A hash join is also applied for a Cartesian product—that
          is, when no join condition is specified, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43151297"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> t1
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JOIN</span> t2
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>c2 <span class="token operator">&gt;</span> <span class="token number">50</span>\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Inner hash join  (cost=0.70 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Filter</span>: <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>c2 <span class="token operator">&gt;</span> <span class="token number">50</span><span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">            -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   It is not necessary for the join to contain at least one
          equi-join condition in order for a hash join to be used. This
          means that the types of queries which can be optimized using
          hash joins include those in the following list (with
          examples):
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Inner non-equi-join
       </em>
      </span>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42611919"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>c1 <span class="token operator">&lt;</span> t2<span class="token punctuation">.</span>c1\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Filter<span class="token punctuation">:</span> (t1.c1 &lt; t2.c1)  (cost=4.70 rows=12)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Inner</span> <span class="token keyword">hash</span> <span class="token keyword">join</span> <span class="token punctuation">(</span><span class="token keyword">no</span> <span class="token keyword">condition</span><span class="token punctuation">)</span>  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">4.70</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">12</span><span class="token punctuation">)</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.08</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">6</span><span class="token punctuation">)</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">            -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.85</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">6</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Semijoin
       </em>
      </span>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55002136"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>c1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>c2 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span>\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Antijoin
       </em>
      </span>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16667102"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1<span class="token punctuation">)</span>\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Hash antijoin (t1.c1 = t2.c1)  (cost=0.70 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>

<span class="token output">1 row in set, 1 warning (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span>\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>
  Level<span class="token punctuation">:</span> Note
   Code<span class="token punctuation">:</span> 1276
Message<span class="token punctuation">:</span> Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Left outer join
       </em>
      </span>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4308709"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Left hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <span class="emphasis">
       <em>
        Right outer join
       </em>
      </span>
      (observe that MySQL
              rewrites all right outer joins as left outer joins):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72200938"><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">EXPLAIN</span> <span class="token keyword">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>c1 <span class="token operator">=</span> t2<span class="token punctuation">.</span>c1\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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Left hash join (t1.c1 = t2.c1)  (cost=0.70 rows=1)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t2  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">Hash</span>
<span class="token prompt">        -&gt;</span> <span class="token keyword">Table</span> scan <span class="token keyword">on</span> t1  <span class="token punctuation">(</span>cost<span class="token operator">=</span><span class="token number">0.35</span> <span class="token keyword">rows</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   By default, MySQL employs hash joins whenever possible. It is
          possible to control whether hash joins are employed using one
          of the
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     BNL
    </code>
   </a>
   and
   <code class="literal">
    NO_BNL
   </code>
   optimizer hints.
  </p>
  <p>
   Memory usage by hash joins can be controlled using the
   <a class="link" href="server-system-variables.html#sysvar_join_buffer_size">
    <code class="literal">
     join_buffer_size
    </code>
   </a>
   system
          variable; a hash join cannot use more memory than this amount.
          When the memory required for a hash join exceeds the amount
          available, MySQL handles this by using files on disk. If this
          happens, you should be aware that the join may not succeed if
          a hash join cannot fit into memory and it creates more files
          than set for
   <a class="link" href="server-system-variables.html#sysvar_open_files_limit">
    <code class="literal">
     open_files_limit
    </code>
   </a>
   . To avoid
          such problems, make either of the following changes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Increase
      <code class="literal">
       join_buffer_size
      </code>
      so that the
              hash join does not spill over to disk.
     </p>
    </li>
    <li class="listitem">
     <p>
      Increase
      <code class="literal">
       open_files_limit
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Join buffers for hash joins are allocated incrementally; thus,
          you can set
   <a class="link" href="server-system-variables.html#sysvar_join_buffer_size">
    <code class="literal">
     join_buffer_size
    </code>
   </a>
   higher without small queries allocating very large amounts of
          RAM, but outer joins allocate the entire buffer. Hash joins
          are used for outer joins (including antijoins and semijoins)
          as well, so this is no longer an issue.
  </p>
 </div>
 <br/>
</div>
