<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="flow-control-functions">
      </a>
      14.5 Flow Control Functions
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045206787360">
  </a>
  <a class="indexterm" name="idm46045206786288">
  </a>
  <div class="table">
   <a name="idm46045206784800">
   </a>
   <p class="title">
    <b>
     Table 14.7 Flow Control Operators
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="A reference that lists flow control operators.">
     <colgroup>
      <col style="width: 28%"/>
      <col style="width: 71%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Name
       </th>
       <th>
        Description
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <a class="link" href="flow-control-functions.html#operator_case">
         <code class="literal">
          CASE
         </code>
        </a>
       </td>
       <td>
        Case operator
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="flow-control-functions.html#function_if">
         <code class="literal">
          IF()
         </code>
        </a>
       </td>
       <td>
        If/else construct
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="flow-control-functions.html#function_ifnull">
         <code class="literal">
          IFNULL()
         </code>
        </a>
       </td>
       <td>
        Null if/else construct
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="flow-control-functions.html#function_nullif">
         <code class="literal">
          NULLIF()
         </code>
        </a>
       </td>
       <td>
        Return NULL if expr1 = expr2
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <a name="operator_case">
     </a>
     <p>
      <a class="link" href="flow-control-functions.html#operator_case">
       <code class="literal">
        CASE
        <em class="replaceable">
         <code>
          value
         </code>
        </em>
        WHEN
        <em class="replaceable">
         <code>
          compare_value
         </code>
        </em>
        THEN
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        [WHEN
        <em class="replaceable">
         <code>
          compare_value
         </code>
        </em>
        THEN
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        ...] [ELSE
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        ] END
       </code>
      </a>
     </p>
     <p>
      <a class="link" href="flow-control-functions.html#operator_case">
       <code class="literal">
        CASE WHEN
        <em class="replaceable">
         <code>
          condition
         </code>
        </em>
        THEN
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        [WHEN
        <em class="replaceable">
         <code>
          condition
         </code>
        </em>
        THEN
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        ...] [ELSE
        <em class="replaceable">
         <code>
          result
         </code>
        </em>
        ] END
       </code>
      </a>
     </p>
     <a class="indexterm" name="idm46045206755040">
     </a>
     <p>
      The first
      <a class="link" href="flow-control-functions.html#operator_case">
       <code class="literal">
        CASE
       </code>
      </a>
      syntax returns the
      <em class="replaceable">
       <code>
        result
       </code>
      </em>
      for the first
      <code class="literal">
       <em class="replaceable">
        <code>
         value
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         compare_value
        </code>
       </em>
      </code>
      comparison that is true. The second syntax returns the result
          for the first condition that is true. If no comparison or
          condition is true, the result after
      <code class="literal">
       ELSE
      </code>
      is
          returned, or
      <code class="literal">
       NULL
      </code>
      if there is no
      <code class="literal">
       ELSE
      </code>
      part.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       The syntax of the
       <a class="link" href="flow-control-functions.html#operator_case">
        <code class="literal">
         CASE
        </code>
       </a>
       <span class="emphasis">
        <em>
         operator
        </em>
       </span>
       described here differs
            slightly from that of the SQL
       <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
        <code class="literal">
         CASE
        </code>
       </a>
       <span class="emphasis">
        <em>
         statement
        </em>
       </span>
       described in
       <a class="xref" href="case.html" title="15.6.5.1 CASE Statement">
        Section 15.6.5.1, “CASE Statement”
       </a>
       , for use inside stored programs. The
       <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
        <code class="literal">
         CASE
        </code>
       </a>
       statement cannot have an
       <code class="literal">
        ELSE NULL
       </code>
       clause, and it is terminated
            with
       <code class="literal">
        END CASE
       </code>
       instead of
       <code class="literal">
        END
       </code>
       .
      </p>
     </div>
     <p>
      The return type of a
      <a class="link" href="flow-control-functions.html#operator_case">
       <code class="literal">
        CASE
       </code>
      </a>
      expression result is the aggregated type of all result values:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If all types are numeric, the aggregated type is also
              numeric:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            If at least one argument is double precision, the
                  result is double precision.
           </p>
          </li>
          <li class="listitem">
           <p>
            Otherwise, if at least one argument is
            <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
             <code class="literal">
              DECIMAL
             </code>
            </a>
            , the result is
            <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
             <code class="literal">
              DECIMAL
             </code>
            </a>
            .
           </p>
          </li>
          <li class="listitem">
           <p>
            Otherwise, the result is an integer type (with one
                  exception):
           </p>
           <div class="itemizedlist">
            <ul class="itemizedlist" style="list-style-type: disc; ">
             <li class="listitem">
              <p>
               If all integer types are all signed or all
                      unsigned, the result is the same sign and the
                      precision is the highest of all specified integer
                      types (that is,
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 TINYINT
                </code>
               </a>
               ,
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 SMALLINT
                </code>
               </a>
               ,
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 MEDIUMINT
                </code>
               </a>
               ,
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 INT
                </code>
               </a>
               , or
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 BIGINT
                </code>
               </a>
               ).
              </p>
             </li>
             <li class="listitem">
              <p>
               If there is a combination of signed and unsigned
                      integer types, the result is signed and the
                      precision may be higher. For example, if the types
                      are signed
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 INT
                </code>
               </a>
               and
                      unsigned
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 INT
                </code>
               </a>
               , the
                      result is signed
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 BIGINT
                </code>
               </a>
               .
              </p>
             </li>
             <li class="listitem">
              <p>
               The exception is unsigned
               <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
                <code class="literal">
                 BIGINT
                </code>
               </a>
               combined
                      with any signed integer type. The result is
               <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
                <code class="literal">
                 DECIMAL
                </code>
               </a>
               with
                      sufficient precision and scale 0.
              </p>
             </li>
            </ul>
           </div>
          </li>
         </ul>
        </div>
       </li>
       <li class="listitem">
        <p>
         If all types are
         <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
          <code class="literal">
           BIT
          </code>
         </a>
         , the
              result is
         <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
          <code class="literal">
           BIT
          </code>
         </a>
         . Otherwise,
         <a class="link" href="bit-type.html" title="13.1.5 Bit-Value Type - BIT">
          <code class="literal">
           BIT
          </code>
         </a>
         arguments are treated
              similar to
         <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
          <code class="literal">
           BIGINT
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If all types are
         <a class="link" href="year.html" title="13.2.4 The YEAR Type">
          <code class="literal">
           YEAR
          </code>
         </a>
         , the
              result is
         <a class="link" href="year.html" title="13.2.4 The YEAR Type">
          <code class="literal">
           YEAR
          </code>
         </a>
         . Otherwise,
         <code class="literal">
          YEAR
         </code>
         arguments are treated similar to
         <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
          <code class="literal">
           INT
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If all types are character string
              (
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           CHAR
          </code>
         </a>
         or
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         ), the result is
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         with maximum length
              determined by the longest character length of the
              operands.
        </p>
       </li>
       <li class="listitem">
        <p>
         If all types are character or binary string, the result is
         <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
          <code class="literal">
           VARBINARY
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="set.html" title="13.3.6 The SET Type">
          <code class="literal">
           SET
          </code>
         </a>
         and
         <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
          <code class="literal">
           ENUM
          </code>
         </a>
         are treated similar to
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         ; the result is
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If all types are
         <a class="link" href="json.html" title="13.5 The JSON Data Type">
          <code class="literal">
           JSON
          </code>
         </a>
         , the
              result is
         <a class="link" href="json.html" title="13.5 The JSON Data Type">
          <code class="literal">
           JSON
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If all types are temporal, the result is temporal:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            If all temporal types are
            <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
             <code class="literal">
              DATE
             </code>
            </a>
            ,
            <a class="link" href="time.html" title="13.2.3 The TIME Type">
             <code class="literal">
              TIME
             </code>
            </a>
            , or
            <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
             <code class="literal">
              TIMESTAMP
             </code>
            </a>
            , the result
                  is
            <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
             <code class="literal">
              DATE
             </code>
            </a>
            ,
            <a class="link" href="time.html" title="13.2.3 The TIME Type">
             <code class="literal">
              TIME
             </code>
            </a>
            , or
            <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
             <code class="literal">
              TIMESTAMP
             </code>
            </a>
            ,
                  respectively.
           </p>
          </li>
          <li class="listitem">
           <p>
            Otherwise, for a mix of temporal types, the result is
            <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
             <code class="literal">
              DATETIME
             </code>
            </a>
            .
           </p>
          </li>
         </ul>
        </div>
       </li>
       <li class="listitem">
        <p>
         If all types are
         <code class="literal">
          GEOMETRY
         </code>
         , the result
              is
         <code class="literal">
          GEOMETRY
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If any type is
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         , the
              result is
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         For all other type combinations, the result is
         <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
          <code class="literal">
           VARCHAR
          </code>
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Literal
         <code class="literal">
          NULL
         </code>
         operands are ignored for
              type aggregation.
        </p>
       </li>
      </ul>
     </div>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46420354"><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">CASE</span> <span class="token number">1</span> <span class="token keyword">WHEN</span> <span class="token number">1</span> <span class="token keyword">THEN</span> <span class="token string">'one'</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHEN</span> <span class="token number">2</span> <span class="token keyword">THEN</span> <span class="token string">'two'</span> <span class="token keyword">ELSE</span> <span class="token string">'more'</span> <span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'one'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">CASE</span> <span class="token keyword">WHEN</span> <span class="token number">1</span><span class="token operator">&gt;</span><span class="token number">0</span> <span class="token keyword">THEN</span> <span class="token string">'true'</span> <span class="token keyword">ELSE</span> <span class="token string">'false'</span> <span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'true'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">CASE</span> <span class="token datatype">BINARY</span> <span class="token string">'B'</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHEN</span> <span class="token string">'a'</span> <span class="token keyword">THEN</span> <span class="token number">1</span> <span class="token keyword">WHEN</span> <span class="token string">'b'</span> <span class="token keyword">THEN</span> <span class="token number">2</span> <span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token boolean">NULL</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <a name="function_if">
     </a>
     <p>
      <a class="link" href="flow-control-functions.html#function_if">
       <code class="literal">
        IF(
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          expr2
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          expr3
         </code>
        </em>
        )
       </code>
      </a>
     </p>
     <a class="indexterm" name="idm46045206655344">
     </a>
     <p>
      If
      <em class="replaceable">
       <code>
        expr1
       </code>
      </em>
      is
      <code class="literal">
       TRUE
      </code>
      (
      <code class="literal">
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       &lt;&gt;
          0
      </code>
      and
      <code class="literal">
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       IS
          NOT NULL
      </code>
      ),
      <a class="link" href="flow-control-functions.html#function_if">
       <code class="literal">
        IF()
       </code>
      </a>
      returns
      <em class="replaceable">
       <code>
        expr2
       </code>
      </em>
      . Otherwise, it
          returns
      <em class="replaceable">
       <code>
        expr3
       </code>
      </em>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       There is also an
       <a class="link" href="if.html" title="15.6.5.2 IF Statement">
        <code class="literal">
         IF
        </code>
       </a>
       <span class="emphasis">
        <em>
         statement
        </em>
       </span>
       , which differs from the
       <a class="link" href="flow-control-functions.html#function_if">
        <code class="literal">
         IF()
        </code>
       </a>
       <span class="emphasis">
        <em>
         function
        </em>
       </span>
       described here. See
       <a class="xref" href="if.html" title="15.6.5.2 IF Statement">
        Section 15.6.5.2, “IF Statement”
       </a>
       .
      </p>
     </div>
     <p>
      If only one of
      <em class="replaceable">
       <code>
        expr2
       </code>
      </em>
      or
      <em class="replaceable">
       <code>
        expr3
       </code>
      </em>
      is explicitly
      <code class="literal">
       NULL
      </code>
      , the result type of the
      <a class="link" href="flow-control-functions.html#function_if">
       <code class="literal">
        IF()
       </code>
      </a>
      function is the type of
          the non-
      <code class="literal">
       NULL
      </code>
      expression.
     </p>
     <p>
      The default return type of
      <a class="link" href="flow-control-functions.html#function_if">
       <code class="literal">
        IF()
       </code>
      </a>
      (which may matter when it is stored into a temporary table) is
          calculated as follows:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If
         <em class="replaceable">
          <code>
           expr2
          </code>
         </em>
         or
         <em class="replaceable">
          <code>
           expr3
          </code>
         </em>
         produce a string, the
              result is a string.
        </p>
        <p>
         If
         <em class="replaceable">
          <code>
           expr2
          </code>
         </em>
         and
         <em class="replaceable">
          <code>
           expr3
          </code>
         </em>
         are both strings, the
              result is case-sensitive if either string is
              case-sensitive.
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <em class="replaceable">
          <code>
           expr2
          </code>
         </em>
         or
         <em class="replaceable">
          <code>
           expr3
          </code>
         </em>
         produce a floating-point
              value, the result is a floating-point value.
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <em class="replaceable">
          <code>
           expr2
          </code>
         </em>
         or
         <em class="replaceable">
          <code>
           expr3
          </code>
         </em>
         produce an integer, the
              result is an integer.
        </p>
       </li>
      </ul>
     </div>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12775564"><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">IF</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">&gt;</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 number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">3</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">IF</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">&lt;</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'yes'</span><span class="token punctuation">,</span><span class="token string">'no'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'yes'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">STRCMP</span><span class="token punctuation">(</span><span class="token string">'test'</span><span class="token punctuation">,</span><span class="token string">'test1'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'no'</span><span class="token punctuation">,</span><span class="token string">'yes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'no'</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <a name="function_ifnull">
     </a>
     <p>
      <a class="link" href="flow-control-functions.html#function_ifnull">
       <code class="literal">
        IFNULL(
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          expr2
         </code>
        </em>
        )
       </code>
      </a>
     </p>
     <a class="indexterm" name="idm46045206620304">
     </a>
     <a class="indexterm" name="idm46045206618816">
     </a>
     <p>
      If
      <em class="replaceable">
       <code>
        expr1
       </code>
      </em>
      is not
      <code class="literal">
       NULL
      </code>
      ,
      <a class="link" href="flow-control-functions.html#function_ifnull">
       <code class="literal">
        IFNULL()
       </code>
      </a>
      returns
      <em class="replaceable">
       <code>
        expr1
       </code>
      </em>
      ; otherwise it returns
      <em class="replaceable">
       <code>
        expr2
       </code>
      </em>
      .
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42453710"><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">IFNULL</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">IFNULL</span><span class="token punctuation">(</span><span class="token boolean">NULL</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 number">10</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">IFNULL</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">/</span><span class="token number">0</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 number">10</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">IFNULL</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">/</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token string">'yes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'yes'</span></code></pre>
     </div>
     <p>
      The default return type of
      <a class="link" href="flow-control-functions.html#function_ifnull">
       <code class="literal">
        IFNULL(
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          expr2
         </code>
        </em>
        )
       </code>
      </a>
      is the more
      <span class="quote">
       “
       <span class="quote">
        general
       </span>
       ”
      </span>
      of the two expressions, in
          the order
      <code class="literal">
       STRING
      </code>
      ,
      <code class="literal">
       REAL
      </code>
      ,
          or
      <code class="literal">
       INTEGER
      </code>
      . Consider the case of a table
          based on expressions or where MySQL must internally store a
          value returned by
      <a class="link" href="flow-control-functions.html#function_ifnull">
       <code class="literal">
        IFNULL()
       </code>
      </a>
      in a
          temporary table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38931563"><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> tmp <span class="token keyword">SELECT</span> <span class="token function">IFNULL</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'test'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> test<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DESCRIBE</span> tmp<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>
<span class="token output"><span class="token punctuation">|</span> Field <span class="token punctuation">|</span> Type         <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <span class="token punctuation">|</span> Extra <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>
<span class="token output"><span class="token punctuation">|</span> test  <span class="token punctuation">|</span> varbinary(4) <span class="token punctuation">|</span> NO   <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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
     </div>
     <p>
      In this example, the type of the
      <code class="literal">
       test
      </code>
      column is
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY(4)
       </code>
      </a>
      (a
          string type).
     </p>
    </li>
    <li class="listitem">
     <a name="function_nullif">
     </a>
     <p>
      <a class="link" href="flow-control-functions.html#function_nullif">
       <code class="literal">
        NULLIF(
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          expr2
         </code>
        </em>
        )
       </code>
      </a>
     </p>
     <a class="indexterm" name="idm46045206591232">
     </a>
     <p>
      Returns
      <code class="literal">
       NULL
      </code>
      if
      <code class="literal">
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         expr2
        </code>
       </em>
      </code>
      is true, otherwise
          returns
      <em class="replaceable">
       <code>
        expr1
       </code>
      </em>
      . This is the same as
      <a class="link" href="flow-control-functions.html#operator_case">
       <code class="literal">
        CASE WHEN
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        =
        <em class="replaceable">
         <code>
          expr2
         </code>
        </em>
        THEN NULL ELSE
        <em class="replaceable">
         <code>
          expr1
         </code>
        </em>
        END
       </code>
      </a>
      .
     </p>
     <p>
      The return value has the same type as the first argument.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93576609"><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">NULLIF</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 prompt">        -&gt;</span> <span class="token boolean">NULL</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">NULLIF</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 prompt">        -&gt;</span> <span class="token number">1</span></code></pre>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       MySQL evaluates
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       twice if
            the arguments are not equal.
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   For each of these functions, if the first argument contains only
      characters present in the character set and collation used by the
      second argument (and it is constant), the latter character set and
      collation is used to make the comparison. System variable values
      are handled as column values of the same character and collation.
      Some queries using these functions with system variables may be
      rejected with
   <span class="errortext">
    Illegal mix of collations
   </span>
   as
      a result. In such cases, you should cast the system variable to
      the correct character set and collation.
  </p>
 </div>
 <br/>
</div>
