<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="optimizing-subqueries">
      </a>
      15.2.15.11 Optimizing Subqueries
     </h4>
    </div>
   </div>
  </div>
  <p>
   Development is ongoing, so no optimization tip is reliable for
        the long term. The following list provides some interesting
        tricks that you might want to play with. See also
   <a class="xref" href="subquery-optimization.html" title="10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions">
    Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table
        Expressions”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Move clauses from outside to inside the subquery. For
            example, use this query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47112548"><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> 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> t1 <span class="token keyword">UNION</span> <span class="token keyword">ALL</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>
      Instead of this query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56955145"><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> 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> t1<span class="token punctuation">)</span> <span class="token operator">OR</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>
      For another example, use this query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa76248112"><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 punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token operator">+</span> <span class="token number">5</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Instead of this query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90708122"><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 punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">5</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
