<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="comparisons-using-subqueries">
      </a>
      15.2.15.2 Comparisons Using Subqueries
     </h4>
    </div>
   </div>
  </div>
  <p>
   The most common use of a subquery is in the form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87561705"><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">non_subquery_operand</em> <em class="replaceable">comparison_operator</em> <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="sa44249406"><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>  <span class="token operator">&lt;=&gt;</span></code></pre>
  </div>
  <p>
   For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa32895026"><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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <span class="token string">'a'</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   MySQL also permits this construct:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa88152287"><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">non_subquery_operand</em> <span class="token operator">LIKE</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   At one time the only legal place for a subquery was on the right
        side of a comparison, and you might still find some old DBMSs
        that insist on this.
  </p>
  <p>
   Here is an example of a common-form subquery comparison that you
        cannot do with a join. It finds all the rows in table
   <code class="literal">
    t1
   </code>
   for which the
   <code class="literal">
    column1
   </code>
   value is equal to a maximum value in table
   <code class="literal">
    t2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7009455"><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> column1 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>column2<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>
   Here is another example, which again is impossible with a join
        because it involves aggregating for one of the tables. It finds
        all rows in table
   <code class="literal">
    t1
   </code>
   containing a value that
        occurs twice in a given column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36201939"><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">AS</span> t
  <span class="token keyword">WHERE</span> <span class="token number">2</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>id <span class="token operator">=</span> t<span class="token punctuation">.</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For a comparison of the subquery to a scalar, the subquery must
        return a scalar. For a comparison of the subquery to a row
        constructor, the subquery must be a row subquery that returns a
        row with the same number of values as the row constructor. See
   <a class="xref" href="row-subqueries.html" title="15.2.15.5 Row Subqueries">
    Section 15.2.15.5, “Row Subqueries”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
