<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="any-in-some-subqueries">
      </a>
      15.2.15.3 Subqueries with ANY, IN, or SOME
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045180022944">
  </a>
  <a class="indexterm" name="idm46045180021488">
  </a>
  <a class="indexterm" name="idm46045180020448">
  </a>
  <a class="indexterm" name="idm46045180019408">
  </a>
  <p>
   Syntax:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71829095"><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">ANY</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
<em class="replaceable">operand</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
<em class="replaceable">operand</em> <em class="replaceable">comparison_operator</em> <span class="token keyword">SOME</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Where
   <em class="replaceable">
    <code>
     comparison_operator
    </code>
   </em>
   is one of
        these operators:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87050935"><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 operator">=</span>  <span class="token operator">&gt;</span>  <span class="token operator">&lt;</span>  <span class="token operator">&gt;=</span>  <span class="token operator">&lt;=</span>  <span class="token operator">&lt;&gt;</span>  <span class="token operator">!=</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ANY
   </code>
   keyword, 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">
      ANY
     </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="sa36484769"><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">ANY</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">
    (21,14,7)
   </code>
   because there is a value
   <code class="literal">
    7
   </code>
   in
   <code class="literal">
    t2
   </code>
   that is less than
   <code class="literal">
    10
   </code>
   . The expression is
   <code class="literal">
    FALSE
   </code>
   if table
   <code class="literal">
    t2
   </code>
   contains
   <code class="literal">
    (20,10)
   </code>
   , or if table
   <code class="literal">
    t2
   </code>
   is
        empty. 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">
    (NULL,NULL,NULL)
   </code>
   .
  </p>
  <p>
   When used with a subquery, the word
   <code class="literal">
    IN
   </code>
   is an
        alias for
   <code class="literal">
    = ANY
   </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="sa19184407"><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">=</span> <span class="token keyword">ANY</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 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>
   <code class="literal">
    IN
   </code>
   and
   <code class="literal">
    = ANY
   </code>
   are not
        synonyms when used with an expression list.
   <code class="literal">
    IN
   </code>
   can take an expression list, but
   <code class="literal">
    = ANY
   </code>
   cannot. See
   <a class="xref" href="comparison-operators.html" title="14.4.2 Comparison Functions and Operators">
    Section 14.4.2, “Comparison Functions and Operators”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    NOT IN
   </code>
   is not an alias for
   <code class="literal">
    &lt;&gt;
        ANY
   </code>
   , but for
   <code class="literal">
    &lt;&gt; ALL
   </code>
   . See
   <a class="xref" href="all-subqueries.html" title="15.2.15.4 Subqueries with ALL">
    Section 15.2.15.4, “Subqueries with ALL”
   </a>
   .
  </p>
  <p>
   The word
   <code class="literal">
    SOME
   </code>
   is an alias for
   <code class="literal">
    ANY
   </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="sa35528711"><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">ANY</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">&lt;&gt;</span> <span class="token keyword">SOME</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>
   Use of the word
   <code class="literal">
    SOME
   </code>
   is rare, but this
        example shows why it might be useful. To most people, the
        English phrase
   <span class="quote">
    “
    <span class="quote">
     a is not equal to any b
    </span>
    ”
   </span>
   means
   <span class="quote">
    “
    <span class="quote">
     there is no b which is equal to a,
    </span>
    ”
   </span>
   but that is
        not what is meant by the SQL syntax. The syntax means
   <span class="quote">
    “
    <span class="quote">
     there is some b to which a is not equal.
    </span>
    ”
   </span>
   Using
   <code class="literal">
    &lt;&gt; SOME
   </code>
   instead helps ensure that
        everyone understands the true meaning of the query.
  </p>
  <p>
   You can use
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   in a scalar
   <code class="literal">
    IN
   </code>
   ,
   <code class="literal">
    ANY
   </code>
   , or
   <code class="literal">
    SOME
   </code>
   subquery provided the table contains
        only a single column. If
   <code class="literal">
    t2
   </code>
   has only one
        column, the statements shown previously in this section can be
        written as shown here, in each case substituting
   <code class="literal">
    TABLE
        t2
   </code>
   for
   <code class="literal">
    SELECT s1 FROM t2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60513270"><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">ANY</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">=</span> <span class="token keyword">ANY</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 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>

<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">ANY</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">&lt;&gt;</span> <span class="token keyword">SOME</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>
 </div>
 <br/>
</div>
