<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="scalar-subqueries">
      </a>
      15.2.15.1 The Subquery as Scalar Operand
     </h4>
    </div>
   </div>
  </div>
  <p>
   In its simplest form, a subquery is a scalar subquery that
        returns a single value. A scalar subquery is a simple operand,
        and you can use it almost anywhere a single column value or
        literal is legal, and you can expect it to have those
        characteristics that all operands have: a data type, a length,
        an indication that it can be
   <code class="literal">
    NULL
   </code>
   , and so on.
        For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89622869"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>s1 <span class="token datatype">INT</span><span class="token punctuation">,</span> s2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token string">'abcde'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s2 <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The subquery in this
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   returns a single value (
   <code class="literal">
    'abcde'
   </code>
   ) that has a
        data type of
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     CHAR
    </code>
   </a>
   , a length of 5,
        a character set and collation equal to the defaults in effect at
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   time, and an
        indication that the value in the column can be
   <code class="literal">
    NULL
   </code>
   . Nullability of the value selected by a
        scalar subquery is not copied because if the subquery result is
        empty, the result is
   <code class="literal">
    NULL
   </code>
   . For the subquery
        just shown, if
   <code class="literal">
    t1
   </code>
   were empty, the result
        would be
   <code class="literal">
    NULL
   </code>
   even though
   <code class="literal">
    s2
   </code>
   is
   <code class="literal">
    NOT NULL
   </code>
   .
  </p>
  <p>
   There are a few contexts in which a scalar subquery cannot be
        used. If a statement permits only a literal value, you cannot
        use a subquery. For example,
   <code class="literal">
    LIMIT
   </code>
   requires
        literal integer arguments, and
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD
        DATA
    </code>
   </a>
   requires a literal string file name. You cannot
        use subqueries to supply these values.
  </p>
  <p>
   When you see examples in the following sections that contain the
        rather spartan construct
   <code class="literal">
    (SELECT column1 FROM
        t1)
   </code>
   , imagine that your own code contains much more
        diverse and complex constructions.
  </p>
  <p>
   Suppose that we make two tables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55521098"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>s1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</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">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>s1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Then perform a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84855267"><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> s1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The result is
   <code class="literal">
    2
   </code>
   because there is a row in
   <code class="literal">
    t2
   </code>
   containing a column
   <code class="literal">
    s1
   </code>
   that has a value of
   <code class="literal">
    2
   </code>
   .
  </p>
  <p>
   The preceding query can also be written like this, using
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa51364742"><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">TABLE</span> t2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A scalar subquery can be part of an expression, but remember the
        parentheses, even if the subquery is an operand that provides an
        argument for a function. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89749608"><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 function">UPPER</span><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 punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The same result can be obtained using
   <code class="literal">
    SELECT
        UPPER((TABLE t1)) FROM t2
   </code>
   .
  </p>
 </div>
 <br/>
</div>
