<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="if">
      </a>
      15.6.5.2 IF Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045176311136">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72985986"><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">IF</span> <em class="replaceable">search_condition</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em>
    <span class="token punctuation">[</span><span class="token keyword">ELSEIF</span> <em class="replaceable">search_condition</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token punctuation">[</span><span class="token keyword">ELSE</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span>
<span class="token keyword">END</span> <span class="token keyword">IF</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="if.html" title="15.6.5.2 IF Statement">
    <code class="literal">
     IF
    </code>
   </a>
   statement for stored
        programs implements a basic conditional construct.
  </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="flow-control-functions.html#function_if">
     <code class="literal">
      IF()
     </code>
    </a>
    <span class="emphasis">
     <em>
      function
     </em>
    </span>
    , which differs from the
    <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>
    described here. See
    <a class="xref" href="flow-control-functions.html" title="14.5 Flow Control Functions">
     Section 14.5, “Flow Control Functions”
    </a>
    . The
    <a class="link" href="if.html" title="15.6.5.2 IF Statement">
     <code class="literal">
      IF
     </code>
    </a>
    statement can have
    <code class="literal">
     THEN
    </code>
    ,
    <code class="literal">
     ELSE
    </code>
    , and
    <code class="literal">
     ELSEIF
    </code>
    clauses, and it is terminated with
    <code class="literal">
     END IF
    </code>
    .
   </p>
  </div>
  <p>
   If a given
   <em class="replaceable">
    <code>
     search_condition
    </code>
   </em>
   evaluates
        to true, the corresponding
   <code class="literal">
    THEN
   </code>
   or
   <code class="literal">
    ELSEIF
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes. If no
   <em class="replaceable">
    <code>
     search_condition
    </code>
   </em>
   matches, the
   <code class="literal">
    ELSE
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes.
  </p>
  <p>
   Each
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   consists of one
        or more SQL statements; an empty
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   is not permitted.
  </p>
  <p>
   An
   <code class="literal">
    IF ... END IF
   </code>
   block, like all other
        flow-control blocks used within stored programs, must be
        terminated with a semicolon, as shown in this example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21649589"><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">DELIMITER</span> <span class="token comment" spellcheck="true">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> SimpleCompare<span class="token punctuation">(</span>n <span class="token datatype">INT</span><span class="token punctuation">,</span> m <span class="token datatype">INT</span><span class="token punctuation">)</span>
  <span class="token keyword">RETURNS</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>

  <span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> s <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">IF</span> n <span class="token operator">&gt;</span> m <span class="token keyword">THEN</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'&gt;'</span><span class="token punctuation">;</span>
    <span class="token keyword">ELSEIF</span> n <span class="token operator">=</span> m <span class="token keyword">THEN</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'='</span><span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'&lt;'</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

    <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> s<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> m<span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">RETURN</span> s<span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token comment" spellcheck="true">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As with other flow-control constructs,
   <code class="literal">
    IF ... END
        IF
   </code>
   blocks may be nested within other flow-control
        constructs, including other
   <a class="link" href="if.html" title="15.6.5.2 IF Statement">
    <code class="literal">
     IF
    </code>
   </a>
   statements. Each
   <a class="link" href="if.html" title="15.6.5.2 IF Statement">
    <code class="literal">
     IF
    </code>
   </a>
   must be
        terminated by its own
   <code class="literal">
    END IF
   </code>
   followed by a
        semicolon. You can use indentation to make nested flow-control
        blocks more easily readable by humans (although this is not
        required by MySQL), as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92501967"><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">DELIMITER</span> <span class="token comment" spellcheck="true">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> VerboseCompare <span class="token punctuation">(</span>n <span class="token datatype">INT</span><span class="token punctuation">,</span> m <span class="token datatype">INT</span><span class="token punctuation">)</span>
  <span class="token keyword">RETURNS</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span>

  <span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> s <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">IF</span> n <span class="token operator">=</span> m <span class="token keyword">THEN</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'equals'</span><span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
      <span class="token keyword">IF</span> n <span class="token operator">&gt;</span> m <span class="token keyword">THEN</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'greater'</span><span class="token punctuation">;</span>
      <span class="token keyword">ELSE</span> <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token string">'less'</span><span class="token punctuation">;</span>
      <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

      <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'is '</span><span class="token punctuation">,</span> s<span class="token punctuation">,</span> <span class="token string">' than'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

    <span class="token keyword">SET</span> s <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> s<span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">,</span> m<span class="token punctuation">,</span> <span class="token string">'.'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">RETURN</span> s<span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token comment" spellcheck="true">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this example, the inner
   <a class="link" href="if.html" title="15.6.5.2 IF Statement">
    <code class="literal">
     IF
    </code>
   </a>
   is
        evaluated only if
   <code class="literal">
    n
   </code>
   is not equal to
   <code class="literal">
    m
   </code>
   .
  </p>
 </div>
 <br/>
</div>
