<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="row-subqueries">
      </a>
      15.2.15.5 Row Subqueries
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179918704">
  </a>
  <a class="indexterm" name="idm46045179917632">
  </a>
  <a class="indexterm" name="idm46045179916592">
  </a>
  <a class="indexterm" name="idm46045179915520">
  </a>
  <p>
   Scalar or column subqueries return a single value or a column of
        values. A
   <span class="emphasis">
    <em>
     row subquery
    </em>
   </span>
   is a subquery
        variant that returns a single row and can thus return more than
        one column value. Legal operators for row subquery comparisons
        are:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1425441"><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>
   Here are two examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50997061"><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 punctuation">(</span>col1<span class="token punctuation">,</span>col2<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> col3<span class="token punctuation">,</span> col4 <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<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 keyword">ROW</span><span class="token punctuation">(</span>col1<span class="token punctuation">,</span>col2<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> col3<span class="token punctuation">,</span> col4 <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For both queries, if the table
   <code class="literal">
    t2
   </code>
   contains a
        single row with
   <code class="literal">
    id = 10
   </code>
   , the subquery returns
        a single row. If this row has
   <code class="literal">
    col3
   </code>
   and
   <code class="literal">
    col4
   </code>
   values equal to the
   <code class="literal">
    col1
   </code>
   and
   <code class="literal">
    col2
   </code>
   values of
        any rows in
   <code class="literal">
    t1
   </code>
   , the
   <code class="literal">
    WHERE
   </code>
   expression is
   <code class="literal">
    TRUE
   </code>
   and each query returns
        those
   <code class="literal">
    t1
   </code>
   rows. If the
   <code class="literal">
    t2
   </code>
   row
   <code class="literal">
    col3
   </code>
   and
   <code class="literal">
    col4
   </code>
   values
        are not equal the
   <code class="literal">
    col1
   </code>
   and
   <code class="literal">
    col2
   </code>
   values of any
   <code class="literal">
    t1
   </code>
   row,
        the expression is
   <code class="literal">
    FALSE
   </code>
   and the query returns
        an empty result set. The expression is
   <span class="emphasis">
    <em>
     unknown
    </em>
   </span>
   (that is,
   <code class="literal">
    NULL
   </code>
   )
        if the subquery produces no rows. An error occurs if the
        subquery produces multiple rows because a row subquery can
        return at most one row.
  </p>
  <p>
   For information about how each operator works for row
        comparisons, 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>
   The expressions
   <code class="literal">
    (1,2)
   </code>
   and
   <code class="literal">
    ROW(1,2)
   </code>
   are sometimes called
   <span class="firstterm">
    row constructors
   </span>
   . The two
        are equivalent. The row constructor and the row returned by the
        subquery must contain the same number of values.
  </p>
  <p>
   A row constructor is used for comparisons with subqueries that
        return two or more columns. When a subquery returns a single
        column, this is regarded as a scalar value and not as a row, so
        a row constructor cannot be used with a subquery that does not
        return at least two columns. Thus, the following query fails
        with a syntax error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14776059"><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 keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Row constructors are legal in other contexts. For example, the
        following two statements are semantically equivalent (and are
        handled in the same way by the optimizer):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59912864"><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 punctuation">(</span>column1<span class="token punctuation">,</span>column2<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<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 number">1</span> <span class="token operator">AND</span> column2 <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following query answers the request,
   <span class="quote">
    “
    <span class="quote">
     find all rows in
        table
     <code class="literal">
      t1
     </code>
     that also exist in table
     <code class="literal">
      t2
     </code>
    </span>
    ”
   </span>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66758527"><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 punctuation">,</span>column2<span class="token punctuation">,</span>column3
  <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>column1<span class="token punctuation">,</span>column2<span class="token punctuation">,</span>column3<span class="token punctuation">)</span> <span class="token keyword">IN</span>
         <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1<span class="token punctuation">,</span>column2<span class="token punctuation">,</span>column3 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For more information about the optimizer and row constructors,
        see
   <a class="xref" href="row-constructor-optimization.html" title="10.2.1.22 Row Constructor Expression Optimization">
    Section 10.2.1.22, “Row Constructor Expression Optimization”
   </a>
  </p>
 </div>
 <br/>
</div>
