<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="problems-with-null">
      </a>
      B.3.4.3 Problems with NULL Values
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053605680">
  </a>
  <a class="indexterm" name="idm46045053604224">
  </a>
  <p>
   The concept of the
   <code class="literal">
    NULL
   </code>
   value is a common
          source of confusion for newcomers to SQL, who often think that
   <code class="literal">
    NULL
   </code>
   is the same thing as an empty string
   <code class="literal">
    ''
   </code>
   . This is not the case. For example, the
          following statements are completely different:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82091774"><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> my_table <span class="token punctuation">(</span>phone<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> my_table <span class="token punctuation">(</span>phone<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Both statements insert a value into the
   <code class="literal">
    phone
   </code>
   column, but the first inserts a
   <code class="literal">
    NULL
   </code>
   value and the second inserts an empty
          string. The meaning of the first can be regarded as
   <span class="quote">
    “
    <span class="quote">
     phone number is not known
    </span>
    ”
   </span>
   and the meaning of
          the second can be regarded as
   <span class="quote">
    “
    <span class="quote">
     the person is known to
          have no phone, and thus no phone number.
    </span>
    ”
   </span>
  </p>
  <p>
   To help with
   <code class="literal">
    NULL
   </code>
   handling, you can use the
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS NULL
    </code>
   </a>
   and
   <a class="link" href="comparison-operators.html#operator_is-not-null">
    <code class="literal">
     IS
          NOT NULL
    </code>
   </a>
   operators and the
   <a class="link" href="flow-control-functions.html#function_ifnull">
    <code class="literal">
     IFNULL()
    </code>
   </a>
   function.
  </p>
  <p>
   In SQL, the
   <code class="literal">
    NULL
   </code>
   value is never true in
          comparison to any other value, even
   <code class="literal">
    NULL
   </code>
   .
          An expression that contains
   <code class="literal">
    NULL
   </code>
   always
          produces a
   <code class="literal">
    NULL
   </code>
   value unless otherwise
          indicated in the documentation for the operators and functions
          involved in the expression. All columns in the following
          example return
   <code class="literal">
    NULL
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa99608300"><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 boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token operator">+</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'Invisible'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To search for column values that are
   <code class="literal">
    NULL
   </code>
   ,
          you cannot use an
   <code class="literal">
    expr = NULL
   </code>
   test. The
          following statement returns no rows, because
   <code class="literal">
    expr =
          NULL
   </code>
   is never true for any expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa4286172"><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> my_table <span class="token keyword">WHERE</span> phone <span class="token operator">=</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To look for
   <code class="literal">
    NULL
   </code>
   values, you must use the
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS NULL
    </code>
   </a>
   test. The following
          statements show how to find the
   <code class="literal">
    NULL
   </code>
   phone
          number and the empty phone number:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50360280"><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> my_table <span class="token keyword">WHERE</span> phone <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</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> my_table <span class="token keyword">WHERE</span> phone <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="working-with-null.html" title="5.3.4.6 Working with NULL Values">
    Section 5.3.4.6, “Working with NULL Values”
   </a>
   , for additional
          information and examples.
  </p>
  <p>
   You can add an index on a column that can have
   <code class="literal">
    NULL
   </code>
   values if you are using the
   <code class="literal">
    MyISAM
   </code>
   ,
   <code class="literal">
    InnoDB
   </code>
   , or
   <code class="literal">
    MEMORY
   </code>
   storage engine. Otherwise, you must
          declare an indexed column
   <code class="literal">
    NOT NULL
   </code>
   , and you
          cannot insert
   <code class="literal">
    NULL
   </code>
   into the column.
  </p>
  <a class="indexterm" name="idm46045053568192">
  </a>
  <p>
   When reading data with
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD
          DATA
    </code>
   </a>
   , empty or missing columns are updated with
   <code class="literal">
    ''
   </code>
   . To load a
   <code class="literal">
    NULL
   </code>
   value
          into a column, use
   <code class="literal">
    \N
   </code>
   in the data file. The
          literal word
   <code class="literal">
    NULL
   </code>
   may also be used under
          some circumstances. See
   <a class="xref" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    Section 15.2.9, “LOAD DATA Statement”
   </a>
   .
  </p>
  <p>
   When using
   <code class="literal">
    DISTINCT
   </code>
   ,
   <code class="literal">
    GROUP
          BY
   </code>
   , or
   <code class="literal">
    ORDER BY
   </code>
   , all
   <code class="literal">
    NULL
   </code>
   values are regarded as equal.
  </p>
  <p>
   When using
   <code class="literal">
    ORDER BY
   </code>
   ,
   <code class="literal">
    NULL
   </code>
   values are presented first, or last if
          you specify
   <code class="literal">
    DESC
   </code>
   to sort in descending
          order.
  </p>
  <p>
   Aggregate (group) functions such as
   <a class="link" href="aggregate-functions.html#function_count">
    <code class="literal">
     COUNT()
    </code>
   </a>
   ,
   <a class="link" href="aggregate-functions.html#function_min">
    <code class="literal">
     MIN()
    </code>
   </a>
   , and
   <a class="link" href="aggregate-functions.html#function_sum">
    <code class="literal">
     SUM()
    </code>
   </a>
   ignore
   <code class="literal">
    NULL
   </code>
   values. The exception to this is
   <a class="link" href="aggregate-functions.html#function_count">
    <code class="literal">
     COUNT(*)
    </code>
   </a>
   , which counts rows and
          not individual column values. For example, the following
          statement produces two counts. The first is a count of the
          number of rows in the table, and the second is a count of the
          number of non-
   <code class="literal">
    NULL
   </code>
   values in the
   <code class="literal">
    age
   </code>
   column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20557210"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> person<span class="token punctuation">;</span></code></pre>
  </div>
  <a class="indexterm" name="idm46045053546448">
  </a>
  <a class="indexterm" name="idm46045053544960">
  </a>
  <a class="indexterm" name="idm46045053543472">
  </a>
  <a class="indexterm" name="idm46045053541984">
  </a>
  <p>
   For some data types, MySQL handles
   <code class="literal">
    NULL
   </code>
   values in special ways. For example, if you insert
   <code class="literal">
    NULL
   </code>
   into an integer or floating-point
          column that has the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute, the next number in the sequence is inserted. Under
          certain conditions, if you insert
   <code class="literal">
    NULL
   </code>
   into
          a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column, the current
          date and time is inserted; this behavior depends in part on
          the server SQL mode (see
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   ) as well
          as the value of the
   <a class="link" href="server-system-variables.html#sysvar_explicit_defaults_for_timestamp">
    <code class="literal">
     explicit_defaults_for_timestamp
    </code>
   </a>
   system variable.
  </p>
 </div>
 <br/>
</div>
