<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="is-null-optimization">
      </a>
      10.2.1.15 IS NULL Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045229028336">
  </a>
  <a class="indexterm" name="idm46045229027264">
  </a>
  <p>
   MySQL can perform the same optimization on
   <em class="replaceable">
    <code>
     col_name
    </code>
   </em>
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   that it can use for
   <em class="replaceable">
    <code>
     col_name
    </code>
   </em>
   <code class="literal">
    =
   </code>
   <em class="replaceable">
    <code>
     constant_value
    </code>
   </em>
   . For example, MySQL
          can use indexes and ranges to search for
   <code class="literal">
    NULL
   </code>
   with
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   .
  </p>
  <p>
   Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49889266"><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> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_col</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_col</em> <span class="token operator">&lt;=&gt;</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_col</em><span class="token operator">=</span><em class="replaceable">const1</em> <span class="token operator">OR</span> <em class="replaceable">key_col</em><span class="token operator">=</span><em class="replaceable">const2</em> <span class="token operator">OR</span> <em class="replaceable">key_col</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If a
   <code class="literal">
    WHERE
   </code>
   clause includes a
   <em class="replaceable">
    <code>
     col_name
    </code>
   </em>
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   condition for a column that is declared as
   <code class="literal">
    NOT NULL
   </code>
   , that expression is optimized
          away. This optimization does not occur in cases when the
          column might produce
   <code class="literal">
    NULL
   </code>
   anyway (for
          example, if it comes from a table on the right side of a
   <code class="literal">
    LEFT JOIN
   </code>
   ).
  </p>
  <p>
   MySQL can also optimize the combination
   <code class="literal">
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    =
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    OR
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    IS NULL
   </code>
   , a form
          that is common in resolved subqueries.
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   shows
   <a class="link" href="explain-output.html#jointype_ref_or_null">
    <code class="literal">
     ref_or_null
    </code>
   </a>
   when this
          optimization is used.
  </p>
  <p>
   This optimization can handle one
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   for any key part.
  </p>
  <p>
   Some examples of queries that are optimized, assuming that
          there is an index on columns
   <code class="literal">
    a
   </code>
   and
   <code class="literal">
    b
   </code>
   of table
   <code class="literal">
    t2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47747358"><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">WHERE</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span><em class="replaceable">expr</em> <span class="token operator">OR</span> t1<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</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 punctuation">,</span> t2 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">OR</span> t2<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</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 punctuation">,</span> t2
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">OR</span> t2<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span> <span class="token operator">AND</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t1<span class="token punctuation">.</span>b<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 punctuation">,</span> t2
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">AND</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t1<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</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 punctuation">,</span> t2
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">AND</span> t2<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
  <span class="token operator">OR</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">AND</span> t2<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <a class="link" href="explain-output.html#jointype_ref_or_null">
    <code class="literal">
     ref_or_null
    </code>
   </a>
   works by first
          doing a read on the reference key, and then a separate search
          for rows with a
   <code class="literal">
    NULL
   </code>
   key value.
  </p>
  <p>
   The optimization can handle only one
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   level. In the following query, MySQL uses key
          lookups only on the expression
   <code class="literal">
    (t1.a=t2.a AND t2.a IS
          NULL)
   </code>
   and is not able to use the key part on
   <code class="literal">
    b
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50482137"><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 punctuation">,</span> t2
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a <span class="token operator">AND</span> t2<span class="token punctuation">.</span>a <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span>
  <span class="token operator">OR</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>b<span class="token operator">=</span>t2<span class="token punctuation">.</span>b <span class="token operator">AND</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
