891 lines
58 KiB
HTML
891 lines
58 KiB
HTML
<?xml version="1.0" encoding="utf-8"?>
|
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
|
|
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow"/>
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="keywords" content="subselect, in CREATE VIEW statement, fullselect,
|
|
SELECT statement, scalar-subselect, as syntax component, SELECT clause,
|
|
clause of subselect, ALL clause, DISTINCT clause, in subselect, asterisk (*),
|
|
* (asterisk), notation, select list, expression, AS clause, name,
|
|
results with subselect, GROUP BY clause, HAVING clause, application,
|
|
in result columns, null value in SQL, in a result, column,
|
|
result columns of subselect, result columns, data type, of subselect,
|
|
FROM clause, table reference, nested table expression, correlation clause,
|
|
derived table, exposed name, exposed, table function, correlation name,
|
|
joined-table, in JOIN clause, search condition, named columns join,
|
|
in FROM clause, JOIN clause, INNER JOIN clause, LEFT JOIN clause,
|
|
LEFT OUTER JOIN clause, outer join, RIGHT JOIN clause, RIGHT OUTER JOIN clause,
|
|
LEFT EXCEPTION JOIN clause, RIGHT EXCEPTION JOIN clause, CROSS JOIN clause,
|
|
WHERE clause, with WHERE, grouping, in grouping expressions, with HAVING,
|
|
in HAVING clause, subquery" />
|
|
<title>subselect</title>
|
|
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
|
|
<link rel="stylesheet" type="text/css" href="ic.css" />
|
|
</head>
|
|
<body>
|
|
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
|
|
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
|
|
|
|
|
|
<a name="subselect"></a>
|
|
<h2 id="subselect"><a href="rbafzmst02.htm#ToC_661">subselect</a></h2>
|
|
<a href="rbafzmstsubselect.htm#synsubselect"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn223.htm"
|
|
border="0" /></span><a href="#skipsyn-222"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-<span class="italic">select-clause</span>--<span class="italic">from-clause</span>--+--------------+----------------->
|
|
'-<span class="italic">where-clause</span>-'
|
|
|
|
>--+-----------------+--+---------------+----------------------><
|
|
'-<span class="italic">group-by-clause</span>-' '-<span class="italic">having-clause</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-222" id="skipsyn-222"></a><a id="idx1167" name="idx1167"></a><a id="idx1168" name="idx1168"></a><a id="idx1169" name="idx1169"></a>
|
|
<a name="synsubselect"></a>
|
|
<p id="synsubselect">The <span class="italic">subselect</span> is a
|
|
component of the fullselect.</p><a id="idx1170" name="idx1170"></a>
|
|
<p>A subselect specifies a result table derived from the tables
|
|
or views identified in the FROM clause. The derivation can be described as
|
|
a sequence of operations in which the result of each operation is input for
|
|
the next. (This is only a way of describing the subselect. The method used
|
|
to perform the derivation may be quite different from this description. If
|
|
portions of the subselect do not actually need to be executed for the correct
|
|
result to be obtained, they may or may not be executed.)</p>
|
|
<p>A <span class="italic">scalar-subselect</span> is a subselect, enclosed in
|
|
parentheses, that returns a single result row and a single result column.
|
|
If the result of the subselect is no rows, then the null value is returned.
|
|
An error is returned if there is more than one row in the result.</p>
|
|
<p>The sequence of the (hypothetical) operations is: </p>
|
|
<ol type="1">
|
|
<li>FROM clause</li>
|
|
<li>WHERE clause</li>
|
|
<li>GROUP BY clause</li>
|
|
<li>HAVING clause</li>
|
|
<li>SELECT clause</li></ol>
|
|
<a name="wq923"></a>
|
|
<h3 id="wq923"><a href="rbafzmst02.htm#ToC_662">select-clause</a></h3><a id="idx1171" name="idx1171"></a>
|
|
<a href="rbafzmstsubselect.htm#synselect_clause"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq924"></a>
|
|
<div class="fignone" id="wq924">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn224.htm"
|
|
border="0" /></span><a href="#skipsyn-223"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-ALL------.
|
|
>>-SELECT--+----------+----------------------------------------->
|
|
'-DISTINCT-'
|
|
|
|
>--+-<span class="italic">*</span>-------------------------------------------+-------------><
|
|
| .-,---------------------------------------. |
|
|
| V | |
|
|
'---+-<span class="italic">expression</span>--+---------------------+-+-+-'
|
|
| | .-AS-. | |
|
|
| '-+----+--<span class="italic">column-name</span>-' |
|
|
+-<span class="italic">table-name.*</span>------------------------+
|
|
+-<span class="italic">view-name.*</span>-------------------------+
|
|
'-<span class="italic">correlation-name.*</span>------------------'
|
|
|
|
</pre>
|
|
<a name="skipsyn-223" id="skipsyn-223"></a></div>
|
|
<a name="synselect_clause"></a>
|
|
<p id="synselect_clause">The SELECT clause specifies the columns of the final
|
|
result table. The column values are produced by the application of the <span class="italic">select list</span> to R. The select list is the names or expressions
|
|
specified in the SELECT clause, and R is the result of the previous operation
|
|
of the subselect. For example, if the only clauses specified are SELECT,
|
|
FROM, and WHERE, R is the result of that WHERE clause. </p>
|
|
<dl class="parml">
|
|
<dt class="bold"><span class="pk">ALL</span> <a id="idx1172" name="idx1172"></a></dt>
|
|
<dd>Selects all rows of the final result table and does not eliminate duplicates.
|
|
This is the default.
|
|
</dd>
|
|
<dt class="bold"><span class="pk">DISTINCT</span> <a id="idx1173" name="idx1173"></a></dt>
|
|
<dd>Eliminates all but one of each set of duplicate rows of the final result
|
|
table. Two rows are duplicates of one another only if each value in the first
|
|
row is equal to the corresponding value in the second row. (For determining
|
|
duplicate rows, two null values are considered equal.) Sort sequence is also
|
|
used for determining distinct values.
|
|
<p>DISTINCT is not allowed if the <span class="italic">select list</span> contains a LOB or DATALINK column.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq925"></a>
|
|
<h4 id="wq925">Select list notation</h4><a id="idx1174" name="idx1174"></a><a id="idx1175" name="idx1175"></a><a id="idx1176" name="idx1176"></a>
|
|
<dl class="parml">
|
|
<dt class="bold">*</dt>
|
|
<dd>Represents a list of columns of table R in the order the columns are
|
|
produced by the FROM clause. The list of names is established when the statement
|
|
containing the SELECT clause is prepared. Therefore, * does not identify
|
|
any columns that have been added to a table after the statement has been
|
|
prepared.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">expression</var> </dt><a id="idx1177" name="idx1177"></a>
|
|
<dd>Specifies the values of a result column. Each <var class="pv">column-name</var> in
|
|
the <var class="pv">expression</var> must unambiguously identify a column of R.
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">column-name</var><span class="base"> or </span> <span class="pk">AS</span> <var class="pv">column-name</var> </dt><a id="idx1178" name="idx1178"></a>
|
|
<dd>Names or renames the result column. The name must not be qualified and
|
|
does not have to be unique.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">name.*</var> </dt><a id="idx1179" name="idx1179"></a>
|
|
<dd>Represents a list of columns of <var class="pv">name</var> in the order the columns
|
|
are produced by the FROM clause. The <var class="pv">name</var> can be a table name, view
|
|
name, or correlation name, and must designate an exposed table, view, or correlation
|
|
name in the FROM clause immediately following the SELECT clause. The first
|
|
name in the list identifies the first column of the table or view, the second
|
|
name in the list identifies the second column of the table or view, and so
|
|
on.
|
|
<p>The list of names is established when the statement containing the
|
|
SELECT clause is prepared. Therefore, * does not identify any columns
|
|
that have been added to a table after the statement has been prepared.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>Normally, when SQL statements are implicitly rebound, the list of names
|
|
is not re-established. Therefore, the number of columns returned by the statement
|
|
does not change. However, there are four cases where the list of names is
|
|
established again and the number of columns can change: </p>
|
|
<ul>
|
|
<li>When an SQL program or SQL package is saved and then restored on an iSeries system
|
|
that is not the same release as the system from which it was saved.</li>
|
|
<li>When SQL naming is specified for an SQL program or package and the owner
|
|
of the program has changed since the SQL program or package was created.</li>
|
|
<li>When an SQL statement is executed for the first time after the install
|
|
of a more recent release of i5/OS.</li>
|
|
<li>When the SELECT * occurs in the fullselect of an INSERT statement
|
|
or in a fullselect within a predicate, and a table or view referenced in the
|
|
fullselect has been deleted and recreated with additional columns.</li></ul>
|
|
<p>The number of columns in the result of SELECT is the same as the number
|
|
of expressions in the operational form of the select list (that is, the list
|
|
established at prepare time), and cannot exceed 8000. The result of a subquery
|
|
must be a single expression, unless the subquery is used in the EXISTS predicate.</p>
|
|
<a name="wq926"></a>
|
|
<h4 id="wq926">Applying the select list</h4>
|
|
<p>The results of applying the select list to R depend on whether or not GROUP
|
|
BY or HAVING is used:</p>
|
|
<a name="selgbys"></a>
|
|
<h5 id="selgbys">If GROUP BY or HAVING is used</h5>
|
|
<ul>
|
|
<li>Each <var class="pv">column-name</var> in the select list must identify a grouping
|
|
expression or be specified within an aggregate function:
|
|
<ul>
|
|
<li>If the grouping expression is a column name, the select list may apply
|
|
additional operators to the column name. For example, if the grouping expression
|
|
is a column C1, the select list may contain C1+1.</li>
|
|
<li>If the grouping expression is not a column name, the select list may not
|
|
apply additional operators to the expression. For example, if the grouping
|
|
expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.</li></ul></li>
|
|
<li>The RRN, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM,
|
|
and HASHED_VALUE functions cannot be specified in the select list.</li>
|
|
<li>The select list is applied to each group of R, and the result
|
|
contains as many rows as there are groups in R. When the select list is applied
|
|
to a group of R, that group is the source of the arguments of the aggregate
|
|
functions in the select list.</li></ul>
|
|
<a name="selgbno"></a>
|
|
<h5 id="selgbno">If neither GROUP BY nor HAVING is used</h5><a id="idx1180" name="idx1180"></a><a id="idx1181" name="idx1181"></a><a id="idx1182" name="idx1182"></a>
|
|
<p></p>
|
|
<ul>
|
|
<li>The select list must not include any aggregate functions, or
|
|
each <var class="pv">column-name</var> must be specified in an aggregate function or be
|
|
a correlated reference.</li>
|
|
<li>If the select list does not include aggregate functions, it is applied
|
|
to each row of R and the result contains as many rows as there are rows in
|
|
R.</li>
|
|
<li>If the select list is a list of aggregate functions, R is the source of
|
|
the arguments of the functions and the result of applying the select list
|
|
is one row.</li></ul>
|
|
<p>In either case the <span class="italic">n</span>th column of the result contains
|
|
the values specified by applying the <span class="italic">n</span>th expression
|
|
in the operational form of the select list.</p>
|
|
<a name="selnull"></a>
|
|
<h4 id="selnull">Null attributes of result columns</h4><a id="idx1183" name="idx1183"></a><a id="idx1184" name="idx1184"></a>
|
|
<p>Result columns allow null values if they are derived from: </p>
|
|
<ul>
|
|
<li>Any aggregate function but COUNT and COUNT_BIG</li>
|
|
<li>Any column that allows null values</li>
|
|
<li>A scalar function or expression with an operand that allows null values</li>
|
|
<li>A host variable that has an indicator variable, or in the case of Java™, a variable or expression whose type is able to represent a Java null value</li>
|
|
<li>A result of a UNION or INTERSECT if at least one of the corresponding
|
|
items in the select list is nullable</li>
|
|
<li>An arithmetic expression in the outer select list</li>
|
|
<li>A scalar fullselect</li>
|
|
<li>A user-defined scalar or table function</li></ul>
|
|
<a name="nrcmj"></a>
|
|
<h4 id="nrcmj">Names of result columns</h4>
|
|
<ul>
|
|
<li>If the AS clause is specified, the name of the result column is the name
|
|
specified on the AS clause.</li>
|
|
<li>If the AS clause is not specified and a column list is specified in the
|
|
correlation clause, the name of the result column is the corresponding name
|
|
in the correlation column list.</li>
|
|
<li>If neither an AS clause nor a column list in the correlation clause is
|
|
specified and the result column is derived only from a single column (without
|
|
any functions or operators), then the result column name is the unqualified
|
|
name of that column.</li>
|
|
<li>All other result columns are unnamed.</li></ul>
|
|
<a name="seldesc"></a>
|
|
<h4 id="seldesc">Data types of result columns</h4><a id="idx1185" name="idx1185"></a><a id="idx1186" name="idx1186"></a>
|
|
<p>Each column of the result of SELECT acquires a data type from the expression
|
|
from which it is derived.</p>
|
|
<a name="wq927"></a>
|
|
<table id="wq927" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq928" width="33%" align="left">When the expression is:</th>
|
|
<th id="wq929" width="66%" align="left">The data type of the result column is:</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td headers="wq928">the name of any numeric column</td>
|
|
<td headers="wq929">the same as the data type of the column, with the same precision and
|
|
scale for decimal columns.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">an integer constant</td>
|
|
<td headers="wq929">INTEGER or BIGINT (if the value of the constant is outside the range
|
|
of INTEGER, but within the range of BIGINT).</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">a decimal or floating-point constant</td>
|
|
<td headers="wq929">the same as the data type of the constant, with the same precision
|
|
and scale for decimal constants.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of any numeric variable</td>
|
|
<td headers="wq929">the same as the data type of the variable, with the same precision
|
|
and scale for decimal variables. If the data type of the variable is not identical
|
|
to an SQL data type (for example, DISPLAY SIGN LEADING SEPARATE in COBOL),
|
|
the result column is decimal.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">an expression</td>
|
|
<td headers="wq929">the same as the data type of the result, with the same precision and
|
|
scale for decimal results as described under <a href="rbafzmstch2expr.htm#ch2expr">Expressions</a>.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">any function</td>
|
|
<td headers="wq929">the data type of the result of the function. For a built-in function,
|
|
see <a href="rbafzmstch2func.htm#ch2func">Built-in functions</a> to determine the data type of the
|
|
result. For a user-defined function, the data type of the result is what was
|
|
defined in the CREATE FUNCTION statement for the function.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of any string column</td>
|
|
<td headers="wq929">the same as the data type of the column, with the same length attribute.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of any string variable</td>
|
|
<td headers="wq929">the same as the data type of the variable, with a length attribute
|
|
equal to the length of the variable. If the data type of the variable is not
|
|
identical to an SQL data type (for example, a NUL-terminated string in C),
|
|
the result column is a varying-length string.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">a character-string constant of length <var class="pv">n</var></td>
|
|
<td headers="wq929">VARCHAR(<var class="pv">n</var>)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">a graphic-string constant of length <var class="pv">n</var></td>
|
|
<td headers="wq929">VARGRAPHIC(<var class="pv">n</var>)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of a datetime column, or an ILE RPG compiler or ILE COBOL compiler datetime host
|
|
variable</td>
|
|
<td headers="wq929">the same as the data type of the column or variable.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of a datalink column</td>
|
|
<td headers="wq929">a datalink, with the same length attribute.</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928">the name of a row ID column or a row ID variable</td>
|
|
<td headers="wq929">ROWID</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq928"><span>the name of a distinct type column</span></td>
|
|
<td headers="wq929"><span>the same as the distinct type of the column, with the same length,
|
|
precision, and scale attributes, if any.</span></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<a name="fromclause"></a>
|
|
<h3 id="fromclause"><a href="rbafzmst02.htm#ToC_670">from-clause</a></h3><a id="idx1187" name="idx1187"></a>
|
|
<a href="rbafzmstsubselect.htm#synfrom"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq930"></a>
|
|
<div class="fignone" id="wq930">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn225.htm"
|
|
border="0" /></span><a href="#skipsyn-224"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-,---------------.
|
|
V |
|
|
>>-FROM----<span class="italic">table-reference</span>-+-----------------------------------><
|
|
|
|
</pre>
|
|
<a name="skipsyn-224" id="skipsyn-224"></a></div>
|
|
<a name="synfrom"></a>
|
|
<p id="synfrom">The FROM clause specifies an intermediate result table.</p>
|
|
<p>If only one <span class="italic">table-reference</span> is specified, the intermediate
|
|
result table is simply the result of that <span class="italic">table-reference</span>. If more than one <span class="italic">table-reference</span> is specified
|
|
in the FROM clause, the intermediate result table consists of all possible
|
|
combinations of the rows of the specified <span class="italic">table-references</span> (the Cartesian product). Each row of the result is a row from the first <span class="italic">table-reference</span> concatenated with a row from the second <span class="italic">table-reference</span>, concatenated in turn with a row from
|
|
the third, and so on. The number of rows in the result is the product of the
|
|
number of rows in all the individual <span class="italic">table-references</span>.</p>
|
|
<a name="tabref"></a>
|
|
<h4 id="tabref">table-reference</h4>
|
|
<p> </p><a id="idx1188" name="idx1188"></a><a id="idx1189" name="idx1189"></a><a id="idx1190" name="idx1190"></a>
|
|
<a href="rbafzmstsubselect.htm#synselect_tbl"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq931"></a>
|
|
<div class="fignone" id="wq931">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn226.htm"
|
|
border="0" /></span><a href="#skipsyn-225"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-+-<span class="italic">single-table</span>------------+---------------------------------><
|
|
+-<span class="italic">nested-table-expression</span>-+
|
|
+-<span class="italic">table-function</span>----------+
|
|
'-<span class="italic">joined-table</span>------------'
|
|
|
|
single-table:
|
|
|
|
|--+-<span class="italic">table-name</span>-+--+--------------------+-----------------------|
|
|
'-<span class="italic">view-name</span>--' '-<span class="italic">correlation-clause</span>-'
|
|
|
|
nested-table-expression:
|
|
|
|
|--+---------+--(--<span class="italic">fullselect</span>--+-----------------+--+--------------------+--)-->
|
|
'-LATERAL-' '-<span class="italic">order-by-clause</span>-' '-<span class="italic">fetch-first-clause</span>-'
|
|
|
|
>--<span class="italic">correlation-clause</span>-------------------------------------------|
|
|
|
|
table-function:
|
|
|
|
|--TABLE--(--<span class="italic">function-invocation</span>--)--<span class="italic">correlation-clause</span>---------|
|
|
|
|
correlation-clause:
|
|
|
|
.-AS-.
|
|
|--+----+--<span class="italic">correlation-name</span>--+-----------------------+----------|
|
|
| .-,-----------. |
|
|
| V | |
|
|
'-(----<span class="italic">column-name</span>-+--)-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-225" id="skipsyn-225"></a></div>
|
|
<p> </p><a id="idx1191" name="idx1191"></a><a id="idx1192" name="idx1192"></a>
|
|
<a name="synselect_tbl"></a>
|
|
<p id="synselect_tbl">A <span class="italic">table-reference</span> specifies
|
|
an intermediate result table.</p>
|
|
<ul>
|
|
<li>If a single table or view is identified, the intermediate result table
|
|
is simply that table or view.</li>
|
|
<li>A fullselect in parentheses called a <span class="italic">nested table expression</span>.<sup class="fn"><a id="wq932" name="wq932" href="rbafzmstsubselect.htm#wq933">53</a></sup> If a nested table expression
|
|
is specified, the result table is the result of that nested table expression.
|
|
The columns of the result do not need unique names, but a column with a non-unique
|
|
name cannot be explicitly referenced.</li>
|
|
<li>If a <span class="italic">function-name</span> is specified, the intermediate
|
|
result table is the set of rows returned by the table function.</li>
|
|
<li>If a <span class="italic">joined-table</span> is specified, the intermediate
|
|
result table is the result of one or more join operations. For more information,
|
|
see <a href="rbafzmstsubselect.htm#joinedt">joined-table</a>.</li></ul>
|
|
<p>If <span class="italic">function-name</span> is specified, the TABLE
|
|
or LATERAL keyword is specified, or a <span class="italic">table-reference</span> identifies
|
|
a distributed table, a table that has a read trigger, or logical file built
|
|
over multiple physical file members; the query cannot contain:</p>
|
|
<ul>
|
|
<li>EXCEPT or INTERSECT operations,</li>
|
|
<li>OLAP specifications,</li>
|
|
<li>recursive common table expressions,</li>
|
|
<li>ORDER OF, or</li>
|
|
<li>scalar fullselects (scalar subselects are supported).</li></ul><a id="idx1193" name="idx1193"></a><a id="idx1194" name="idx1194"></a><a id="idx1195" name="idx1195"></a>
|
|
<p>The list of names in the FROM clause must conform to these rules: </p>
|
|
<ul>
|
|
<li>Each <span class="italic">table-name</span> and <span class="italic">view-name</span> must name an existing table or view at the current server or
|
|
the <span class="italic">table-identifier</span> of a common table expression
|
|
defined preceding the subselect containing the <span class="italic">table-reference</span>.</li>
|
|
<li>The exposed names must be unique. An exposed name is a <span class="italic">correlation-name</span>, a <span class="italic">table-name</span> that is not followed
|
|
by a <span class="italic">correlation-name</span>, or a <span class="italic">view-name</span> that is not followed by a <span class="italic">correlation-name</span>.</li>
|
|
<li>Each <span class="italic">function-name</span>, together with the types of
|
|
its arguments, must resolve to a table function that exists at the current
|
|
server. An algorithm called function resolution, which is described on <a href="rbafzmstch2function.htm#funcres">Function resolution</a>, uses the function name and the arguments to determine the
|
|
exact function to use. Unless given column names in the <span class="italic">correlation-clause</span>, the column names for a table function are those specified
|
|
on the RETURNS clause of the CREATE FUNCTION statement. This is analogous
|
|
to the column names of a table, which are defined in the CREATE TABLE.</li></ul><a id="idx1196" name="idx1196"></a>
|
|
<p>Each <span class="italic">correlation-name</span> is defined as a designator
|
|
of the intermediate result table specified by the immediately preceding <span class="italic">table-reference</span>. A <span class="italic">correlation-name</span> must be specified for nested table expressions and table functions.</p>
|
|
<p>The exposed names of all table references should be unique. An exposed
|
|
name is: </p>
|
|
<ul>
|
|
<li>A <span class="italic">correlation-name</span></li>
|
|
<li>A <span class="italic">table-name</span> or <span class="italic">view-name</span> that
|
|
is not followed by a <span class="italic">correlation-name</span></li></ul>
|
|
<p>Any qualified reference to a column for a table, view, nested table expression,
|
|
or table function must use the exposed name. If the same table name or view
|
|
name is specified twice, at least one specification should be followed by
|
|
a <span class="italic">correlation-name</span>. The <span class="italic">correlation-name</span> is used to qualify references to the columns of the table or view. When
|
|
a <span class="italic">correlation-name</span> is specified, column-names can
|
|
also be specified to give names to the columns of the <span class="italic">table-name</span>, <span class="italic">view-name</span>, <span class="italic">nested-table-expression</span> or <span class="italic">table-function</span>. If a column list is specified,
|
|
there must be a name in the column list for each column in the table or view
|
|
and for each result column in the <span class="italic">nested-table-expression</span> or <span class="italic">table-function</span>. For more information, see <a href="rbafzmstch2col.htm#c2cornm">Correlation names</a>.</p>
|
|
<p>In general, <span class="italic">nested-table-expressions</span> and <span class="italic">table-functions</span> can be specified in any FROM clause. Columns from the
|
|
nested table expressions and table functions can be referenced in the select
|
|
list and in the rest of the subselect using the correlation name which must
|
|
be specified. The scope of this correlation name is the same as correlation
|
|
names for other table or view names in the FROM clause. A nested table expression
|
|
can be used: </p>
|
|
<ul>
|
|
<li>in place of a view to avoid creating the view (when general use of the
|
|
view is not required)</li>
|
|
<li>when the desired result table is based on variables.</li></ul>
|
|
<a name="wq934"></a>
|
|
<h5 id="wq934">Correlated references in table-references</h5>
|
|
<p>Correlated references can be used in <span class="italic">nested-table-expressions</span>. The basic rule that applies is that the correlated reference must be
|
|
from a <span class="italic">table-reference</span> at a higher level in the hierarchy
|
|
of subqueries. This hierarchy includes the <var class="pv">table-references</var> that
|
|
have already been resolved in the left-to-right processing of the FROM clause.
|
|
For nested table expressions, the TABLE or LATERAL keyword must appear before
|
|
the fullselect. For more information see <a href="rbafzmstch2col.htm#qcn1">Column name qualifiers to avoid ambiguity</a></p>
|
|
<p>A table function can contain one or more correlated references
|
|
to other tables in the same FROM clause if the referenced tables precede the
|
|
reference in the left-to-right order of the tables in the FROM clause. The
|
|
same capability exists for nested table expressions if the optional keyword
|
|
TABLE or LATERAL is specified. Otherwise, only references to higher levels
|
|
in the hierarchy of subqueries is allowed.</p>
|
|
<p>A nested table expression or table function that contains correlated references
|
|
to other tables in the same FROM clause:</p>
|
|
<ul>
|
|
<li>Cannot participate in a RIGHT OUTER JOIN or RIGHT EXCEPTION JOIN</li>
|
|
<li>Can participate in LEFT OUTER JOIN or an INNER JOIN if the referenced
|
|
tables precede the reference in the left-to-right order of the tables in the
|
|
FROM clause</li></ul>
|
|
<p>A nested table expression cannot contain a correlated reference to other
|
|
tables in the same FROM clause when:</p>
|
|
<ul>
|
|
<li>The nested table expression contains a UNION, EXCEPT, or INTERSECT.</li>
|
|
<li>The nested table expression uses the DISTINCT keyword in the select list.</li>
|
|
<li>The nested table expression contains an ORDER BY and FETCH
|
|
FIRST clause.</li>
|
|
<li>The nested table expression is in the FROM clause of another nested table
|
|
expression that contains one of these restrictions.</li></ul>
|
|
<p><span class="bold">Syntax Alternatives:</span> TABLE can be specified in place
|
|
of LATERAL.</p>
|
|
<a name="wq935"></a>
|
|
<h6 id="wq935">Example 1</h6>
|
|
<p>The following example is valid: </p>
|
|
<pre class="xmp"><span class="bold">SELECT</span> D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
|
|
<span class="bold">FROM</span> DEPARTMENT D,
|
|
<span class="bold">(SELECT AVG(</span>E.SALARY<span class="bold">) AS</span> AVGSAL,<span class="bold">COUNT (*) AS</span> EMPCOUNT
|
|
<span class="bold">FROM</span> EMPLOYEE E
|
|
<span class="bold">WHERE</span> E.WORKDEPT =
|
|
<span class="bold">(SELECT</span> X.DEPTNO
|
|
<span class="bold">FROM</span> DEPARTMENT X
|
|
<span class="bold">WHERE</span> X.DEPTNO = E.WORKDEPT <span class="bold">) )</span> <span class="bold">AS</span> EMPINFO </pre>
|
|
<p>The following example is not valid because the reference to D.DEPTNO in
|
|
the WHERE clause of the <span class="italic">nested-table-expression</span> attempts
|
|
to reference a table that is outside the hierarchy of subqueries: </p>
|
|
<pre class="xmp"><span class="bold">SELECT</span> D.DEPTNO, D.DEPTNAME,
|
|
EMPINFO.AVGSAL, EMPINFO.EMPCOUNT ***INCORRECT***
|
|
<span class="bold">FROM</span> DEPARTMENT D,
|
|
<span class="bold">(SELECT AVG(</span>E.SALARY<span class="bold">) AS</span> AVGSAL,<span class="bold">COUNT (*) AS</span> EMPCOUNT
|
|
<span class="bold">FROM</span> EMPLOYEE E
|
|
<span class="bold">WHERE</span> E.WORKDEPT = D.DEPTNO <span class="bold">) AS</span> EMPINFO</pre>
|
|
<p>The following example is valid because the reference to D.DEPTNO in the
|
|
WHERE clause of the <span class="italic">nested-table-expression</span> references
|
|
DEPT, which precedes the <span class="italic">nested-table-expression</span> and
|
|
the LATERAL keyword was specified: </p>
|
|
<pre class="xmp"><span class="bold">SELECT</span> D.DEPTNO, D.DEPTNAME,
|
|
EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
|
|
<span class="bold">FROM</span> DEPARTMENT D,
|
|
<span class="bold">LATERAL (SELECT AVG(</span>E.SALARY<span class="bold">) AS</span> AVGSAL,<span class="bold">COUNT (*) AS</span> EMPCOUNT
|
|
<span class="bold">FROM</span> EMPLOYEE E
|
|
<span class="bold">WHERE</span> E.WORKDEPT = D.DEPTNO <span class="bold">) AS</span> EMPINFO</pre>
|
|
<a name="wq936"></a>
|
|
<h6 id="wq936">Example 2</h6>
|
|
<p>The following example of a table function is valid: </p>
|
|
<pre class="xmp"><span class="bold">SELECT</span> t.c1, z.c5
|
|
<span class="bold">FROM</span> t, <span class="bold">TABLE(</span>tf3 (t.c2 ) <span class="bold">) AS</span> z <span class="bold">WHERE</span> t.c3 = z.c4
|
|
</pre>
|
|
<p>The following example is not valid because the reference to t.c2 is for
|
|
a table that is to the right of the table function in the FROM clause: </p>
|
|
<pre class="xmp"><span class="bold">SELECT</span> t.c1, z.c5
|
|
<span class="bold">FROM</span> <span class="bold">TABLE(</span>tf6 (t.c2 ) <span class="bold">) AS</span> z, t ***INCORRECT***
|
|
<span class="bold">WHERE</span> t.c3 = z.c4
|
|
</pre>
|
|
<a name="joinedt"></a>
|
|
<h4 id="joinedt">joined-table</h4><a id="idx1197" name="idx1197"></a>
|
|
<p> </p>
|
|
<a href="rbafzmstsubselect.htm#synselect_join"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq937"></a>
|
|
<div class="fignone" id="wq937">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn227.htm"
|
|
border="0" /></span><a href="#skipsyn-226"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-INNER----------------.
|
|
|--+-<span class="italic">table-reference</span>--+-+----------------------+--JOIN--<span class="italic">table-reference</span>--+-ON--<span class="italic">join-condition</span>-----------+-+-+--|
|
|
| | | .-OUTER-. | | .-,-----------. | | |
|
|
| | +-+-LEFT--+--+-------+-+ | V | | | |
|
|
| | | '-RIGHT-' | '-USING--(----<span class="italic">column-name</span>-+--)-' | |
|
|
| | | .-LEFT--. | | |
|
|
| | '-+-------+--EXCEPTION-' | |
|
|
| | '-RIGHT-' | |
|
|
| '-CROSS JOIN--<span class="italic">table-reference</span>-------------------------------------------------------' |
|
|
'-(--<span class="italic">joined-table</span>--)-------------------------------------------------------------------------------------'
|
|
|
|
</pre>
|
|
<a name="skipsyn-226" id="skipsyn-226"></a></div>
|
|
<p> </p>
|
|
<a name="synselect_join"></a>
|
|
<p id="synselect_join">A <span class="italic">joined-table</span> specifies an
|
|
intermediate result table that is the result of either an inner, outer, cross,
|
|
or exception join. The table is derived by applying one of the join operators:
|
|
INNER, LEFT OUTER, RIGHT OUTER, LEFT EXCEPTION, RIGHT EXCEPTION or CROSS to
|
|
its operands.</p>
|
|
<p>If a join operator is not specified, INNER is implicit. The order in which
|
|
multiple joins are performed can affect the result. Joins can be nested within
|
|
other joins. The order of processing for joins is generally from left to right,
|
|
but based on the position of the required <span class="italic">join-condition</span> or USING clause. Parentheses are recommended to make the order of nested
|
|
joins more readable. For example: </p>
|
|
<pre class="xmp"> TB1 <span class="bold">LEFT JOIN</span> TB2 <span class="bold">ON</span> TB1.C1=TB2.C1
|
|
<span class="bold">LEFT JOIN</span> TB3 <span class="bold">LEFT JOIN</span> TB4 <span class="bold">ON</span> TB3.C1=TB4.C1
|
|
<span class="bold">ON</span> TB1.C1=TB3.C1
|
|
</pre><p class="indatacontent">is the same as</p>
|
|
<pre class="xmp"> <span class="bold">(</span>TB1 <span class="bold">LEFT JOIN</span> TB2 <span class="bold">ON</span> TB1.C1=TB2.C1<span class="bold">)</span>
|
|
<span class="bold">LEFT JOIN</span> <span class="bold">(</span>TB3 <span class="bold">LEFT JOIN</span> TB4 <span class="bold">ON</span> TB3.C1=TB4.C1<span class="bold">)</span>
|
|
<span class="bold">ON</span> TB1.C1=TB3.C1
|
|
</pre>
|
|
<p>An inner join combines each row of the left table with every row of the
|
|
right table keeping only the rows where the <span class="italic">join-condition</span> (or USING clause) is true. Thus, the result table may be missing rows
|
|
of from either or both of the joined tables. Outer joins include the rows
|
|
produced by the inner join as well as the missing rows, depending on the type
|
|
of outer join. Exception joins include only the missing rows, depending on
|
|
the type of exception join as follows:</p>
|
|
<ul>
|
|
<li><span class="italic">Left outer.</span> Includes the rows from the left table
|
|
that were missing from the inner join.</li>
|
|
<li><span class="italic">Right outer.</span> Includes the rows from the right
|
|
table that were missing from the inner join.</li>
|
|
<li><span class="italic">Left exception.</span> Includes only the rows from the
|
|
left table that were missing from the inner join.</li>
|
|
<li><span class="italic">Right exception.</span> Includes only the rows from the
|
|
right table that were missing from the inner join.</li></ul>
|
|
<p>A joined table can be used in any context in which any form of the SELECT
|
|
statement is used. A view or a cursor is read-only if its SELECT statement
|
|
includes a joined table.</p>
|
|
<a name="wq938"></a>
|
|
<h5 id="wq938">Join condition</h5><a id="idx1198" name="idx1198"></a>
|
|
<p>The <span class="italic">join-condition</span> is a <span class="italic">search-condition</span> that must conform to these rules:</p>
|
|
<ul>
|
|
<li>It cannot contain a quantified subquery, IN predicate with
|
|
a subselect, or EXISTS subquery. It can contain basic predicate subqueries
|
|
and scalar-fullselects.</li>
|
|
<li>Each column name must unambiguously identify a column in one of the tables
|
|
in the <span class="italic"> from-clause</span>.</li>
|
|
<li>Aggregate functions cannot be used in the <span class="italic"> expression</span>.</li>
|
|
<li>Non-deterministic scalar functions cannot be used in the <span class="italic"> expression</span>.</li></ul>
|
|
<p>For any type of join, column references in an expression of the <span class="italic">join-condition</span> are resolved using the rules for resolution of column
|
|
name qualifiers specified in <a href="rbafzmstch2col.htm#ch2col">Column names</a> before any rules about
|
|
which tables the columns must belong to are applied.</p>
|
|
<a name="wq939"></a>
|
|
<h5 id="wq939">Join USING</h5><a id="idx1199" name="idx1199"></a>
|
|
<p>The USING clause specifies a shorthand way of defining the join condition.
|
|
This form is known as a <var class="pv">named-columns-join</var>.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">column-name</var></dt>
|
|
<dd>Must unambiguously identify a column that exists in both <var class="pv">table-references</var> of the joined table. The column must not be a DATALINK column.
|
|
</dd>
|
|
</dl>
|
|
<p>The USING clause is equivalent to a <var class="pv">join-condition</var> in which each
|
|
column from the left <var class="pv">table-reference</var> is compared equal to a column
|
|
of the same name in the right <var class="pv">table-reference</var>. For example, a <var class="pv">named-columns-join</var> of the form:</p>
|
|
<pre class="xmp"> TB1 <span class="bold">INNER JOIN</span> TB2
|
|
<span class="bold">USING (</span>C1, C2, ... Cn<span class="bold">)</span></pre><p class="indatacontent">is
|
|
equivalent to:</p>
|
|
<pre class="xmp"> TB1 <span class="bold">INNER JOIN</span> TB2
|
|
<span class="bold">ON</span> TB1.C1 = TB2.C1 <span class="bold">AND</span>
|
|
TB1.C2 = TB2.C2 <span class="bold">AND</span>
|
|
...
|
|
TB1.Cn = TB2.Cn </pre>
|
|
<a name="wq940"></a>
|
|
<h5 id="wq940">Join operations</h5>
|
|
<p>A <span class="italic">join-condition</span> (or USING clause) specifies pairings
|
|
of T1 and T2, where T1 and T2 are the left and right operand tables of the
|
|
JOIN operator of the <span class="italic">join-condition</span> (or USING clause).
|
|
For all possible combinations of rows of T1 and T2, a row of T1 is paired
|
|
with a row of T2 if the <span class="italic">join-condition</span> (or USING clause)
|
|
is true. When a row of T1 is joined with a row of T2, a row in the result
|
|
consists of the values of that row of T1 concatenated with the values of that
|
|
row of T2. In the case of OUTER joins, the execution might involve the generation
|
|
of a null row. The null row of a table consists of a null value for each column
|
|
of the table, regardless of whether the columns allow null values.</p>
|
|
<dl class="parml">
|
|
<dt class="bold">INNER JOIN <span class="base">or</span> JOIN </dt><a id="idx1200" name="idx1200"></a><a id="idx1201" name="idx1201"></a>
|
|
<dd>The result of T1 INNER JOIN T2 consists of their paired rows.
|
|
<p>Using
|
|
the INNER JOIN syntax with a <span class="italic">join-condition</span> (or USING
|
|
clause) will produce the same result as specifying the join by listing two
|
|
tables in the FROM clause separated by commas and using the <span class="italic">where-clause</span> to provide the condition.</p>
|
|
</dd><a id="idx1202" name="idx1202"></a><a id="idx1203" name="idx1203"></a><a id="idx1204" name="idx1204"></a>
|
|
<dt class="bold">LEFT JOIN <span class="base">or</span> LEFT OUTER JOIN </dt>
|
|
<dd>The result of T1 LEFT OUTER JOIN T2 consists of their paired rows and,
|
|
for each unpaired row of T1, the concatenation of that row with the null row
|
|
of T2. All columns derived from T2 allow null values.
|
|
</dd><a id="idx1205" name="idx1205"></a><a id="idx1206" name="idx1206"></a><a id="idx1207" name="idx1207"></a>
|
|
<dt class="bold">RIGHT JOIN <span class="base">or</span> RIGHT OUTER JOIN </dt>
|
|
<dd>The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows and,
|
|
for each unpaired row of T2, the concatenation of that row with the null row
|
|
of T1. All columns derived from T1 allow null values.
|
|
</dd><a id="idx1208" name="idx1208"></a>
|
|
<dt class="bold">LEFT EXCEPTION JOIN and EXCEPTION JOIN</dt>
|
|
<dd>The result of T1 LEFT EXCEPTION JOIN T2 consists only of each unpaired
|
|
row of T1, the concatenation of that row with the null row of T2. All columns
|
|
derived from T2 allow null values.
|
|
</dd><a id="idx1209" name="idx1209"></a>
|
|
<dt class="bold">RIGHT EXCEPTION JOIN </dt>
|
|
<dd>The result of T1 RIGHT EXCEPTION JOIN T2 consists only of each unpaired
|
|
row of T2, the concatenation of that row with the null row of T1. All columns
|
|
derived from T1 allow null values.
|
|
</dd>
|
|
<dt class="bold">CROSS JOIN <a id="idx1210" name="idx1210"></a></dt>
|
|
<dd>The result of T1 CROSS JOIN T2 consists of each row of T1 paired with
|
|
each row of T2. CROSS JOIN is also known as cartesian product.
|
|
</dd>
|
|
</dl>
|
|
<a name="wq941"></a>
|
|
<h3 id="wq941"><a href="rbafzmst02.htm#ToC_679">where-clause</a></h3><a id="idx1211" name="idx1211"></a><a id="idx1212" name="idx1212"></a>
|
|
<a href="rbafzmstsubselect.htm#synselect_where"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq942"></a>
|
|
<div class="fignone" id="wq942">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn228.htm"
|
|
border="0" /></span><a href="#skipsyn-227"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-WHERE--<span class="italic">search-condition</span>-------------------------------------><
|
|
|
|
</pre>
|
|
<a name="skipsyn-227" id="skipsyn-227"></a></div>
|
|
<a name="synselect_where"></a>
|
|
<p id="synselect_where">The WHERE clause specifies an intermediate result
|
|
table that consists of those rows of R for which the <var class="pv">search-condition</var> is true. R is the result of the FROM clause of the statement.</p>
|
|
<p>The <var class="pv">search-condition</var> must conform to the following rules: </p>
|
|
<ul>
|
|
<li>Each <var class="pv">column-name</var> must unambiguously identify a column of R or
|
|
be a correlated reference. A <var class="pv">column-name</var> is a correlated reference
|
|
if it identifies a column of a table, view, <span class="italic">common-table-expression</span>, or <span class="italic">nested-table-expression</span> identified in an
|
|
outer subselect.</li>
|
|
<li>An aggregate function must not be specified unless the WHERE
|
|
clause is specified in a subquery of a HAVING clause and the argument of the
|
|
function is a correlated reference to a group.</li></ul>
|
|
<p>Any subquery in the <var class="pv">search-condition</var> is effectively executed for
|
|
each row of R and the results are used in the application of the <var class="pv">search-condition</var> to the given row of R. A subquery is executed for each row of R if it
|
|
includes a correlated reference to a column of R. A subquery with no correlated
|
|
reference is typically executed just once.</p>
|
|
<p>If a sort sequence other than *HEX is in effect when the statement that
|
|
contains the WHERE clause is executed and if the <var class="pv">search-condition</var> contains
|
|
operands that are SBCS data, mixed data, or Unicode data, then the comparison
|
|
for those predicates is done using weighted values. The weighted values are
|
|
derived by applying the sort sequence to the operands of the predicate.</p>
|
|
<a name="wq943"></a>
|
|
<h3 id="wq943"><a href="rbafzmst02.htm#ToC_680">group-by-clause</a></h3><a id="idx1213" name="idx1213"></a>
|
|
<p> </p>
|
|
<a href="rbafzmstsubselect.htm#synselect_group"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq944"></a>
|
|
<div class="fignone" id="wq944">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn229.htm"
|
|
border="0" /></span><a href="#skipsyn-228"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-,-------------------.
|
|
V |
|
|
>>-GROUP BY----<span class="italic">grouping-expression</span>-+---------------------------><
|
|
|
|
</pre>
|
|
<a name="skipsyn-228" id="skipsyn-228"></a></div>
|
|
<p> </p>
|
|
<a name="synselect_group"></a>
|
|
<p id="synselect_group">The GROUP BY clause specifies an intermediate result
|
|
table that consists of a grouping of the rows of R. R is the result of the
|
|
previous clause of the subselect.</p>
|
|
<p>A <var class="pv">grouping-expression</var> is an expression that defines
|
|
the grouping of R. The following restrictions apply to <var class="pv">grouping-expression</var>. </p>
|
|
<ul>
|
|
<li>Each column name included in <var class="pv">grouping-expression</var> must unambiguously
|
|
identify a column of R.</li>
|
|
<li>The result of <var class="pv">grouping-expression</var> cannot be a LOB or DataLink
|
|
data type, or a distinct type that is based on a LOB or DataLink.</li>
|
|
<li>The length attribute of each <var class="pv">grouping-expression</var> must
|
|
not be more than 32766, or 32765 if the expression is nullable<sup class="fn"><a id="wq945" name="wq945" href="rbafzmstsubselect.htm#wq946">54</a></sup></li>
|
|
<li><var class="pv">grouping-expression</var> cannot include any of the following items:
|
|
<ul>
|
|
<li>A correlated column</li>
|
|
<li>A variable</li>
|
|
<li>An aggregate function</li>
|
|
<li>Any function that is nondeterministic or the RRN, DATAPARTITIONNAME, DATAPARTITIONNUM,
|
|
DBPARTITIONNAME, DBPARTITIONNUM, and HASHED_VALUE functions</li></ul></li></ul><a id="idx1214" name="idx1214"></a>
|
|
<p>The result of the GROUP BY clause is a set of groups of rows. In each group
|
|
of more than one row, all values of each <var class="pv">grouping-expression</var> are
|
|
equal, and all rows with the same set of values of the <var class="pv">grouping-expressions</var> are in the same group. For grouping, all null values for a <var class="pv">grouping-expression</var> are considered equal.</p><a id="idx1215" name="idx1215"></a>
|
|
<p>Because every row of a group contains the same value of any <var class="pv">grouping-expression</var>, <var class="pv">grouping-expressions</var> can be used in a search condition in
|
|
a HAVING clause, in the SELECT clause, or in a <span class="italic">sort-key-expression</span> of an ORDER BY clause (see <a href="rbafzmstintsel.htm#orderby">order-by-clause</a> for details). In each
|
|
case, the reference specifies only one value for each group. The <var class="pv">grouping-expression</var> specified in these clauses must exactly match the <var class="pv">grouping-expression</var> in the GROUP BY clause, except that blanks are not significant. For
|
|
example, a <span class="italic">grouping-expression</span> of </p>
|
|
<pre class="xmp">SALARY*.10</pre><p class="indatacontent">will match the expression in a <span class="italic">having-clause</span> of</p>
|
|
<pre class="xmp"><span class="bold">HAVING</span> SALARY*.10</pre><p class="indatacontent">but will not match</p>
|
|
<pre class="xmp"><span class="bold">HAVING</span> .10 *SALARY
|
|
or
|
|
<span class="bold">HAVING</span> (SALARY*.10)+100</pre>
|
|
<p>If the <var class="pv">grouping-expression</var> contains varying-length strings with
|
|
trailing blanks, the values in the group can differ in the number of trailing
|
|
blanks and may not all have the same length. In that case, a reference to
|
|
the <var class="pv">grouping-expression</var> still specifies only one value for each group,
|
|
but the value for a group is chosen arbitrarily from the available set of
|
|
values. Thus, the actual length of the result value is unpredictable.</p>
|
|
<p>The number of <var class="pv">grouping-expressions</var> must not exceed
|
|
120 and the sum of their length attributes must not exceed 32766-<span class="italic">n</span> bytes, where <span class="italic">n</span> is the number
|
|
of <var class="pv">grouping-expressions</var> specified that allow nulls.</p>
|
|
<p>If a sort sequence other than *HEX is in effect when the statement that
|
|
contains the GROUP BY clause is executed, and the <var class="pv">grouping-expressions</var> are SBCS data, mixed data, or Unicode data, then the rows are placed
|
|
into groups using the weighted values. The weighted values are derived by
|
|
applying the sort sequence to the <var class="pv">grouping-expressions</var>.</p>
|
|
<a name="havecl"></a>
|
|
<h3 id="havecl"><a href="rbafzmst02.htm#ToC_681">having-clause</a></h3>
|
|
<p> </p>
|
|
<a href="rbafzmstsubselect.htm#synselect_having"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq947"></a>
|
|
<div class="fignone" id="wq947">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn230.htm"
|
|
border="0" /></span><a href="#skipsyn-229"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-HAVING--<span class="italic">search-condition</span>------------------------------------><
|
|
|
|
</pre>
|
|
<a name="skipsyn-229" id="skipsyn-229"></a></div><a id="idx1216" name="idx1216"></a><a id="idx1217" name="idx1217"></a>
|
|
<p> </p>
|
|
<a name="synselect_having"></a>
|
|
<p id="synselect_having">The HAVING clause specifies an intermediate result
|
|
table that consists of those groups of R for which the <var class="pv">search-condition</var> is true. R is the result of the previous clause of the subselect. If
|
|
this clause is not GROUP BY, R is considered a single group with no grouping
|
|
expressions.</p>
|
|
<p>Each expression that contains a <var class="pv">column-name</var> in the search condition
|
|
must do one of the following: </p>
|
|
<ul>
|
|
<li>Unambiguously identify a grouping expression of R.</li>
|
|
<li>Be specified within an aggregate function.</li>
|
|
<li>Be a correlated reference. A <var class="pv">column-name</var> is a correlated reference
|
|
if it identifies a column of a table, view, common table expression, or nested
|
|
table expression identified in an outer subselect.</li></ul>
|
|
<p>The RRN, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME,
|
|
DBPARTITIONNUM, and HASHED_VALUE functions cannot be specified in the HAVING
|
|
clause unless it is within an aggregate function. See "Functions" in Chapter
|
|
3 for restrictions that apply to the use of aggregate functions.</p>
|
|
<p>A group of R to which the search condition is applied supplies
|
|
the argument for each aggregate function in the search condition, except for
|
|
any function whose argument is a correlated reference.</p>
|
|
<p>If the search condition contains a subquery, the subquery can be thought
|
|
of as being executed each time the search condition is applied to a group
|
|
of R, and the results used in applying the search condition. In actuality,
|
|
the subquery is executed for each group only if it contains a correlated reference.
|
|
For an illustration of the difference, see examples 6 and 7 under <a href="rbafzmstsubselect.htm#selctex">Examples of a subselect</a>.</p><a id="idx1218" name="idx1218"></a>
|
|
<p>A correlated reference to a group of R must either identify
|
|
a grouping column or be contained within an aggregate function.</p>
|
|
<p>When HAVING is used without GROUP BY, any column name in the
|
|
select list must appear within an aggregate function.</p>
|
|
<p>If a sort sequence other than *HEX is in effect when the statement that
|
|
contains the HAVING clause is executed and if the <var class="pv">search-condition</var> contains
|
|
operands that have SBCS data, mixed data, or Unicode data, the comparison
|
|
for those predicates is done using weighted values. The weighted values are
|
|
derived by applying the sort sequence to the operands in the predicate.</p>
|
|
<a name="selctex"></a>
|
|
<h3 id="selctex"><a href="rbafzmst02.htm#ToC_682">Examples of a subselect</a></h3>
|
|
<a name="wq948"></a>
|
|
<h4 id="wq948">Example 1</h4>
|
|
<p>Select all columns and rows from the EMPLOYEE table. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT * FROM</span> EMPLOYEE</pre>
|
|
<a name="wq949"></a>
|
|
<h4 id="wq949">Example 2</h4>
|
|
<p>Join the EMPPROJACT and EMPLOYEE tables, select all the columns from the
|
|
EMPPROJACT table and add the employee's surname (LASTNAME) from the EMPLOYEE
|
|
table to each row of the result. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> EMPPROJACT.*, LASTNAME
|
|
<span class="bold">FROM</span> EMPPROJACT, EMPLOYEE
|
|
<span class="bold">WHERE</span> EMPPROJACT.EMPNO = EMPLOYEE.EMPNO</pre>
|
|
<a name="wq950"></a>
|
|
<h4 id="wq950">Example 3</h4>
|
|
<p>Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO),
|
|
employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table
|
|
and DEPTNO in the DEPARTMENT table) and department name (DEPTNAME) of all
|
|
employees who were born (BIRTHDATE) earlier than 1930. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> EMPNO, LASTNAME, WORKDEPT, DEPTNAME
|
|
<span class="bold">FROM</span> EMPLOYEE, DEPARTMENT
|
|
<span class="bold">WHERE</span> WORKDEPT = DEPTNO
|
|
<span class="bold">AND YEAR(</span>BIRTHDATE<span class="bold">)</span> < 1930</pre>
|
|
<p>This subselect could also be written as follows: </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> EMPNO, LASTNAME, WORKDEPT, DEPTNAME
|
|
<span class="bold">FROM</span> EMPLOYEE <span class="bold">INNER JOIN</span> DEPARTMENT
|
|
<span class="bold">ON</span> WORKDEPT = DEPTNO
|
|
<span class="bold">WHERE</span><span class="bold"> YEAR</span>(BIRTHDATE) < 1930</pre>
|
|
<a name="wq951"></a>
|
|
<h4 id="wq951">Example 4</h4>
|
|
<p>Select the job (JOB) and the minimum and maximum salaries (SALARY) for
|
|
each group of rows with the same job code in the EMPLOYEE table, but only
|
|
for groups with more than one row and with a maximum salary greater than or
|
|
equal to 27000. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> JOB, <span class="bold">MIN(</span>SALARY<span class="bold">)</span>, <span class="bold">MAX(</span>SALARY<span class="bold">)</span>
|
|
<span class="bold">FROM</span> EMPLOYEE
|
|
<span class="bold">GROUP BY</span> JOB
|
|
<span class="bold">HAVING COUNT(*)</span> > 1 <span class="bold">AND MAX(</span>SALARY<span class="bold">)</span> >= 27000</pre>
|
|
<a name="wq952"></a>
|
|
<h4 id="wq952">Example 5</h4>
|
|
<p>Select all the rows of EMPPROJACT table for employees (EMPNO) in department
|
|
(WORKDEPT) 'E11'. (Employee department numbers are shown in the EMPLOYEE
|
|
table.) </p>
|
|
<pre class="xmp"> <span class="bold">SELECT * FROM</span> EMPPROJACT
|
|
<span class="bold">WHERE</span> EMPNO <span class="bold">IN (SELECT</span> EMPNO <span class="bold">FROM</span> EMPLOYEE
|
|
<span class="bold">WHERE</span> WORKDEPT = 'E11'<span class="bold">)</span></pre>
|
|
<a name="wq953"></a>
|
|
<h4 id="wq953">Example 6</h4>
|
|
<p>From the EMPLOYEE table, select the department number (WORKDEPT) and maximum
|
|
departmental salary (SALARY) for all departments whose maximum salary is less
|
|
than the average salary for all employees. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> WORKDEPT, <span class="bold">MAX(</span>SALARY<span class="bold">)</span>
|
|
<span class="bold">FROM</span> EMPLOYEE
|
|
<span class="bold">GROUP BY</span> WORKDEPT
|
|
<span class="bold">HAVING MAX(</span>SALARY<span class="bold">) < (SELECT AVG(</span>SALARY<span class="bold">)</span>
|
|
<span class="bold">FROM</span> EMPLOYEE<span class="bold">)</span></pre><p class="indatacontent"> The subquery in the HAVING clause would only be executed once
|
|
in this example.</p>
|
|
<a name="wq954"></a>
|
|
<h4 id="wq954">Example 7</h4>
|
|
<p>Using the EMPLOYEE table, select the department number (WORKDEPT) and maximum
|
|
departmental salary (SALARY) for all departments whose maximum salary is less
|
|
than the average salary in all other departments. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> WORKDEPT, <span class="bold">MAX(</span>SALARY<span class="bold">)</span>
|
|
<span class="bold">FROM</span> EMPLOYEE EMP_COR
|
|
<span class="bold">GROUP BY</span> WORKDEPT
|
|
<span class="bold">HAVING MAX(</span>SALARY<span class="bold">) < (SELECT AVG(</span>SALARY<span class="bold">)</span>
|
|
<span class="bold">FROM</span> EMPLOYEE
|
|
<span class="bold">WHERE NOT</span> WORKDEPT = EMP_COR.WORKDEPT<span class="bold">)</span></pre>
|
|
<p>In contrast to example 6, the subquery in the HAVING clause would need
|
|
to be executed for each group.</p>
|
|
<a name="wq955"></a>
|
|
<h4 id="wq955">Example 8</h4>
|
|
<p>Join the EMPLOYEE and EMPPROJACT tables, select all of the employees and
|
|
their project numbers. Return even those employees that do not have a project
|
|
number currently assigned. </p>
|
|
<pre class="xmp"> <span class="bold">SELECT</span> EMPLOYEE.EMPNO, PROJNO
|
|
<span class="bold">FROM</span> EMPLOYEE <span class="bold">LEFT OUTER JOIN</span> EMPPROJACT
|
|
<span class="bold">ON</span> EMPLOYEE.EMPNO = EMPPROJACT.EMPNO</pre>
|
|
<p>Any employee in the EMPLOYEE table that does not have a project number
|
|
in the EMPPROJACT table will return one row in the result table containing
|
|
the EMPNO value and the null value in the PROJNO column.</p>
|
|
<hr /><div class="fnnum"><a id="wq933" name="wq933" href="rbafzmstsubselect.htm#wq932">53</a>.</div>
|
|
<div class="fntext">A <span class="italic">nested table expression</span> is also called
|
|
a <span class="italic">derived table</span>.</div><div class="fnnum"><a id="wq946" name="wq946" href="rbafzmstsubselect.htm#wq945">54</a>.</div>
|
|
<div class="fntext">If ALWCPYDTA(*NO)
|
|
is specified, the length attribute must not be more than 2000, or 1999 if
|
|
the expression is nullable.</div>
|
|
<br />
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstqueryauth1.htm">Previous Page</a> | <a href="rbafzmstmark.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
|
|
<a href="rbafzmstindex.htm#index">Index</a> ]
|
|
|
|
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
|
|
</body>
|
|
</html>
|