<div class="codehilite"><pre><span></span><code><span class="c1">-- between</span>
<span class="k">SELECT</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">5</span><span class="w"> </span><span class="k">between</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="k">between</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="w">                  </span><span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>

<span class="c1">-- coalesce</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">coalesce</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="k">coalesce</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="w">                      </span><span class="mi">1</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>

<span class="c1">-- if</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">if</span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;b&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="p">(</span><span class="k">IF</span><span class="p">((</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="w">                  </span><span class="n">a</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>

<span class="c1">-- ifnull</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">ifnull</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="s1">&#39;2&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="n">ifnull</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="mi">2</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="w">                   </span><span class="p">[</span><span class="mi">2</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>

<span class="c1">-- nanvl</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nanvl</span><span class="p">(</span><span class="k">cast</span><span class="p">(</span><span class="s1">&#39;NaN&#39;</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">double</span><span class="p">),</span><span class="w"> </span><span class="mi">123</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="n">nanvl</span><span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="n">NaN</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">DOUBLE</span><span class="p">),</span><span class="w"> </span><span class="mi">123</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="w">                          </span><span class="mi">123</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>

<span class="c1">-- nullif</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">nullif</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span>
<span class="o">+</span><span class="c1">------------+</span>
<span class="o">|</span><span class="k">nullif</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+</span>
<span class="o">|</span><span class="w">        </span><span class="k">NULL</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+</span>

<span class="c1">-- nvl</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nvl</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="s1">&#39;2&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="n">nvl</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="mi">2</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="w">                </span><span class="p">[</span><span class="mi">2</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>

<span class="c1">-- nvl2</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nvl2</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="n">nvl2</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="w">               </span><span class="mi">1</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>

<span class="c1">-- when</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="w">                                                        </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>

<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="w">                                                        </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>

<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
<span class="o">|</span><span class="w">                                              </span><span class="k">NULL</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
</code></pre></div>