<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="exists-and-not-exists-subqueries">
      </a>
      15.2.15.6 Subqueries with EXISTS or NOT EXISTS
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179883472">
  </a>
  <a class="indexterm" name="idm46045179881984">
  </a>
  <a class="indexterm" name="idm46045179880496">
  </a>
  <a class="indexterm" name="idm46045179879008">
  </a>
  <p>
   If a subquery returns any rows at all,
   <code class="literal">
    EXISTS
    <em class="replaceable">
     <code>
      subquery
     </code>
    </em>
   </code>
   is
   <code class="literal">
    TRUE
   </code>
   , and
   <code class="literal">
    NOT EXISTS
    <em class="replaceable">
     <code>
      subquery
     </code>
    </em>
   </code>
   is
   <code class="literal">
    FALSE
   </code>
   . For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa88291049"><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> column1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Traditionally, an
   <code class="literal">
    EXISTS
   </code>
   subquery starts with
   <code class="literal">
    SELECT *
   </code>
   , but it could begin with
   <code class="literal">
    SELECT 5
   </code>
   or
   <code class="literal">
    SELECT column1
   </code>
   or anything at all. MySQL ignores the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   list in such a subquery,
        so it makes no difference.
  </p>
  <p>
   For the preceding example, if
   <code class="literal">
    t2
   </code>
   contains any
        rows, even rows with nothing but
   <code class="literal">
    NULL
   </code>
   values,
        the
   <code class="literal">
    EXISTS
   </code>
   condition is
   <code class="literal">
    TRUE
   </code>
   . This is actually an unlikely example
        because a
   <code class="literal">
    [NOT] EXISTS
   </code>
   subquery almost always
        contains correlations. Here are some more realistic examples:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      What kind of store is present in one or more cities?
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa904344"><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 keyword">DISTINCT</span> store_type <span class="token keyword">FROM</span> stores
  <span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cities_stores
                <span class="token keyword">WHERE</span> cities_stores<span class="token punctuation">.</span>store_type <span class="token operator">=</span> stores<span class="token punctuation">.</span>store_type<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      What kind of store is present in no cities?
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56714266"><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 keyword">DISTINCT</span> store_type <span class="token keyword">FROM</span> stores
  <span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cities_stores
                    <span class="token keyword">WHERE</span> cities_stores<span class="token punctuation">.</span>store_type <span class="token operator">=</span> stores<span class="token punctuation">.</span>store_type<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      What kind of store is present in all cities?
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27076457"><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 keyword">DISTINCT</span> store_type <span class="token keyword">FROM</span> stores
  <span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cities <span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span>
      <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cities_stores
       <span class="token keyword">WHERE</span> cities_stores<span class="token punctuation">.</span>city <span class="token operator">=</span> cities<span class="token punctuation">.</span>city
       <span class="token operator">AND</span> cities_stores<span class="token punctuation">.</span>store_type <span class="token operator">=</span> stores<span class="token punctuation">.</span>store_type<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The last example is a double-nested
   <code class="literal">
    NOT
        EXISTS
   </code>
   query. That is, it has a
   <code class="literal">
    NOT
        EXISTS
   </code>
   clause within a
   <code class="literal">
    NOT EXISTS
   </code>
   clause. Formally, it answers the question
   <span class="quote">
    “
    <span class="quote">
     does a city
        exist with a store that is not in
     <code class="literal">
      Stores
     </code>
    </span>
    ”
   </span>
   ? But it is easier to say that
        a nested
   <code class="literal">
    NOT EXISTS
   </code>
   answers the question
   <span class="quote">
    “
    <span class="quote">
     is
     <em class="replaceable">
      <code>
       x
      </code>
     </em>
     <code class="literal">
      TRUE
     </code>
     for all
     <em class="replaceable">
      <code>
       y
      </code>
     </em>
     ?
    </span>
    ”
   </span>
  </p>
  <p>
   You can also use
   <code class="literal">
    NOT EXISTS
   </code>
   or
   <code class="literal">
    NOT
        EXISTS
   </code>
   with
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   in
        the subquery, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36384172"><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> column1 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</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>
   The results are the same as when using
   <code class="literal">
    SELECT
        *
   </code>
   with no
   <code class="literal">
    WHERE
   </code>
   clause in the
        subquery.
  </p>
 </div>
 <br/>
</div>
