<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="all-subqueries">
      </a>
      15.2.15.4 Subqueries with ALL
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179970096">
  </a>
  <a class="indexterm" name="idm46045179968608">
  </a>
  <p>
   Syntax:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14529206"><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"><em class="replaceable">operand</em> <em class="replaceable">comparison_operator</em> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   The word
   <code class="literal">
    ALL
   </code>
   , which must follow a comparison
        operator, means
   <span class="quote">
    “
    <span class="quote">
     return
     <code class="literal">
      TRUE
     </code>
     if the
        comparison is
     <code class="literal">
      TRUE
     </code>
     for
     <code class="literal">
      ALL
     </code>
     of the values in the column that the subquery returns.
    </span>
    ”
   </span>
   For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74113763"><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> s1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token operator">&gt;</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that there is a row in table
   <code class="literal">
    t1
   </code>
   containing
   <code class="literal">
    (10)
   </code>
   . The expression is
   <code class="literal">
    TRUE
   </code>
   if table
   <code class="literal">
    t2
   </code>
   contains
   <code class="literal">
    (-5,0,+5)
   </code>
   because
   <code class="literal">
    10
   </code>
   is
        greater than all three values in
   <code class="literal">
    t2
   </code>
   . The
        expression is
   <code class="literal">
    FALSE
   </code>
   if table
   <code class="literal">
    t2
   </code>
   contains
   <code class="literal">
    (12,6,NULL,-100)
   </code>
   because there is a single
        value
   <code class="literal">
    12
   </code>
   in table
   <code class="literal">
    t2
   </code>
   that
        is greater than
   <code class="literal">
    10
   </code>
   . The expression is
   <span class="emphasis">
    <em>
     unknown
    </em>
   </span>
   (that is,
   <code class="literal">
    NULL
   </code>
   )
        if table
   <code class="literal">
    t2
   </code>
   contains
   <code class="literal">
    (0,NULL,1)
   </code>
   .
  </p>
  <p>
   Finally, the expression is
   <code class="literal">
    TRUE
   </code>
   if table
   <code class="literal">
    t2
   </code>
   is empty. So, the following expression is
   <code class="literal">
    TRUE
   </code>
   when table
   <code class="literal">
    t2
   </code>
   is
        empty:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80536358"><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> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   But this expression is
   <code class="literal">
    NULL
   </code>
   when table
   <code class="literal">
    t2
   </code>
   is empty:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72418902"><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> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In addition, the following expression is
   <code class="literal">
    NULL
   </code>
   when table
   <code class="literal">
    t2
   </code>
   is empty:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa32112493"><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> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In general,
   <span class="emphasis">
    <em>
     tables containing
     <code class="literal">
      NULL
     </code>
     values
    </em>
   </span>
   and
   <span class="emphasis">
    <em>
     empty tables
    </em>
   </span>
   are
   <span class="quote">
    “
    <span class="quote">
     edge cases.
    </span>
    ”
   </span>
   When writing subqueries, always
        consider whether you have taken those two possibilities into
        account.
  </p>
  <p>
   <code class="literal">
    NOT IN
   </code>
   is an alias for
   <code class="literal">
    &lt;&gt;
        ALL
   </code>
   . Thus, these two statements are the same:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26925716"><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> s1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token operator">&lt;&gt;</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As with
   <code class="literal">
    IN
   </code>
   ,
   <code class="literal">
    ANY
   </code>
   , and
   <code class="literal">
    SOME
   </code>
   , you can use
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   with
   <code class="literal">
    ALL
   </code>
   and
   <code class="literal">
    NOT IN
   </code>
   provided that the following two
        conditions are met:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The table in the subquery contains only one column
     </p>
    </li>
    <li class="listitem">
     <p>
      The subquery does not depend on a column expression
     </p>
    </li>
   </ul>
  </div>
  <p>
   For example, assuming that table
   <code class="literal">
    t2
   </code>
   consists
        of a single column, the last two statements shown previously can
        be written using
   <code class="literal">
    TABLE t2
   </code>
   like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44416707"><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> s1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token operator">&lt;&gt;</span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">TABLE</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> s1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">TABLE</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A query such as
   <code class="literal">
    SELECT * FROM t1 WHERE 1 &gt; ALL
        (SELECT MAX(s1) FROM t2);
   </code>
   cannot be written using
   <code class="literal">
    TABLE t2
   </code>
   because the subquery depends on a
        column expression.
  </p>
 </div>
 <br/>
</div>
