<?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>