friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstmark.htm

349 lines
16 KiB
HTML
Raw Permalink Blame History

<?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="fullselect, SELECT statement, description,
subquery, scalar-fullselect, of fullselect, UNION clause, UNION ALL clause,
with duplicate rows, duplicate rows with UNION, EXCEPT clause, INTERSECT clause,
set operation, with UNION, parentheses, with EXCEPT, with INTERSECT, rules,
column" />
<title>fullselect</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="mark"></a>
<h2 id="mark"><a href="rbafzmst02.htm#ToC_691">fullselect</a></h2><a id="idx1219" name="idx1219"></a><a id="idx1220" name="idx1220"></a>
<a href="rbafzmstmark.htm#synfull"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<a name="wq956"></a>
<div class="fignone" id="wq956">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn231.htm"
border="0" /></span><a href="#skipsyn-230"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-+-<span class="italic">subselect</span>----+--------------------------------------------->
'-<span class="italic">(fullselect)</span>-'
.---------------------------------------------------.
V |
>----+-----------------------------------------------+-+------->&lt;
| .-DISTINCT-. |
'-+-UNION--+----------+-----+--+-<span class="italic">subselect</span>----+-'
| '-ALL------' | '-<span class="italic">(fullselect)</span>-'
| .-DISTINCT-. |
+-EXCEPT--+----------+----+
| .-DISTINCT-. |
'-INTERSECT--+----------+-'
</pre>
<a name="skipsyn-230" id="skipsyn-230"></a></div>
<p>The <span class="italic">fullselect</span> is a component of the <var class="pv">select-statement</var> and the CREATE VIEW statement.</p><a id="idx1221" name="idx1221"></a>
<p>A fullselect that is enclosed in parenthesis is called a <var class="pv">subquery</var>.
For example, a <var class="pv">subquery</var> can be used in a search condition.</p><a id="idx1222" name="idx1222"></a>
<p>A <span class="italic">scalar-fullselect</span> is a fullselect,
enclosed in parentheses, that returns a single result row and a single result
column. If the result of the fullselect is no rows, then the null value is
returned. An error is returned if there is more than one row in the result.</p>
<a name="synfull"></a>
<p id="synfull">A <span class="italic">fullselect</span> specifies a result table.
If UNION, EXCEPT, or INTERSECT is not used, the result of the fullselect is
the result of the specified subselect. </p>
<dl class="parml">
<dt class="bold"><span class="pk">UNION DISTINCT</span> <span class="base">or</span> <span class="pk">UNION ALL</span> </dt><a id="idx1223" name="idx1223"></a><a id="idx1224" name="idx1224"></a>
<dd>Derives a result table by combining two other result tables (R1 and
R2). If UNION ALL is specified, the result consists of all rows in R1 and
R2. If UNION is specified without the ALL option, the result is the set of
all rows in either R1 or R2, with duplicate rows eliminated. In either case,
however, each row of the UNION table is either a row from R1 or a row from
R2.
</dd><a id="idx1225" name="idx1225"></a><a id="idx1226" name="idx1226"></a>
<dt class="bold"><span class="pk">EXCEPT DISTINCT</span></dt><a id="idx1227" name="idx1227"></a>
<dd>Derives a result table by combining two other result tables (R1 and
R2). The result consists of all rows that are only in R1, with duplicate rows
in the result of this operation eliminated.
</dd>
<dt class="bold"><span class="pk">INTERSECT DISTINCT</span></dt><a id="idx1228" name="idx1228"></a>
<dd>Derives a result table by combining two other result tables (R1 and
R2). The result consists of all rows that are in both R1 and R2, with the
duplicate rows eliminated.
</dd>
</dl>
<p>If the <span class="italic">n</span>th column of R1 and the <span class="italic">n</span>th column of R2 have the same result column name, then the nth column
of the result table has the result column name. If the <span class="italic">n</span>th column of R1 and the <span class="italic">n</span>th column of R2 do
not have the same names, then the result column is unnamed.</p>
<p>Two rows are duplicates if each value in the first is equal to the corresponding
value of the second. (For determining duplicates, two null values are considered
equal.)</p>
<p>INTERSECT and EXCEPT are not allowed if the query specifies:</p>
<ul>
<li>lateral correlation,</li>
<li>a sort sequence,</li>
<li>an operation that requires CCSID conversion,</li>
<li>a UTF-8 or UTF-16 argument in a CHARACTER_LENGTH, POSITION, or SUBSTRING
scalar function,</li>
<li>a distributed table,</li>
<li>a table with a read trigger, or</li>
<li>a logical file built over multiple physical file members.</li></ul>
<p>If a sort sequence other than *HEX is in effect when the statement that
contains the UNION keyword is executed and if the result tables contain columns
that are SBCS data, mixed data, or Unicode data, the comparison for those
columns is done using weighted values. The weighted values are derived by
applying the sort sequence to each value.</p><a id="idx1229" name="idx1229"></a><a id="idx1230" name="idx1230"></a><a id="idx1231" name="idx1231"></a><a id="idx1232" name="idx1232"></a>
<p>UNION, UNION ALL, and INTERSECT are associative set operations.
However, when UNION, UNION ALL, EXCEPT, and INTERSECT are used in the same
statement, the result depends on the order in which the operations are performed.
Operations within parenthesis are performed first. When the order is not specified
by parentheses, operations are performed in left-to-right order with the exception
that all INTERSECT operations are performed before UNION or EXCEPT operations.</p>
<p>In the following example, the values of tables R1 and R2 are shown on the
left. The other headings listed show the values as a result of various set
operations on R1 and R2.</p>
<a name="wq957"></a>
<table id="wq957" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq958" align="left">R1</th>
<th id="wq959" align="left">R2</th>
<th id="wq960" align="left">UNION ALL</th>
<th id="wq961" align="left">UNION</th>
<th id="wq962" align="left">EXCEPT</th>
<th id="wq963" align="left">INTERSECT</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td headers="wq958">1</td>
<td headers="wq959">1</td>
<td headers="wq960">1</td>
<td headers="wq961">1</td>
<td headers="wq962">2</td>
<td headers="wq963">1</td>
</tr>
<tr>
<td headers="wq958">1</td>
<td headers="wq959">1</td>
<td headers="wq960">1</td>
<td headers="wq961">2</td>
<td headers="wq962">5</td>
<td headers="wq963">3</td>
</tr>
<tr>
<td headers="wq958">1</td>
<td headers="wq959">3</td>
<td headers="wq960">1</td>
<td headers="wq961">3</td>
<td headers="wq962"></td>
<td headers="wq963">4</td>
</tr>
<tr>
<td headers="wq958">2</td>
<td headers="wq959">3</td>
<td headers="wq960">1</td>
<td headers="wq961">4</td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">2</td>
<td headers="wq959">3</td>
<td headers="wq960">1</td>
<td headers="wq961">5</td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">2</td>
<td headers="wq959">3</td>
<td headers="wq960">2</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">3</td>
<td headers="wq959">4</td>
<td headers="wq960">2</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">4</td>
<td headers="wq959"></td>
<td headers="wq960">2</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">4</td>
<td headers="wq959"></td>
<td headers="wq960">3</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958">5</td>
<td headers="wq959"></td>
<td headers="wq960">3</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">3</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">3</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">3</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">4</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">4</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">4</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
<tr>
<td headers="wq958"></td>
<td headers="wq959"></td>
<td headers="wq960">5</td>
<td headers="wq961"></td>
<td headers="wq962"></td>
<td headers="wq963"></td>
</tr>
</tbody>
</table>
<a name="irfrcol"></a>
<h3 id="irfrcol"><a href="rbafzmst02.htm#ToC_692">Rules for columns</a></h3><a id="idx1233" name="idx1233"></a>
<p>R1 and R2 must have the same number of columns, and the data type of the <span class="italic">n</span>th column of R1 must be compatible with the data type
of the <span class="italic">n</span>th column of R2. Character-string values are
compatible with datetime values.</p>
<p>The <span class="italic">n</span>th column of the result of UNION, UNION ALL,
EXCEPT, or INTERSECT is derived from the <span class="italic">n</span>th columns
of R1 and R2. The attributes of the result columns are determined using the
rules for result columns. For more information see <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a>.</p>
<p>If UNION, INTERSECT, or EXCEPT is specified, no column can be a LOB or
DATALINK column.</p>
<a name="wq964"></a>
<h3 id="wq964"><a href="rbafzmst02.htm#ToC_693">Examples of a fullselect</a></h3>
<a name="wq965"></a>
<h4 id="wq965">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="wq966"></a>
<h4 id="wq966">Example 2</h4>
<p>List the employee numbers (EMPNO) of all employees in the EMPLOYEE table
whose department number (WORKDEPT) either begins with 'E' <span class="bold">or</span> who are assigned to projects in the EMPPROJACT table whose project
number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'. </p>
<pre class="xmp"> <span class="bold">SELECT</span> EMPNO <span class="bold">FROM</span> EMPLOYEE
<span class="bold">WHERE</span> WORKDEPT <span class="bold">LIKE</span> 'E%'
<span class="bold">UNION</span>
<span class="bold">SELECT</span> EMPNO <span class="bold">FROM</span> EMPPROJACT
<span class="bold">WHERE</span> PROJNO <span class="bold">IN(</span>'MA2100', 'MA2110', 'MA2112'<span class="bold">)</span></pre>
<a name="wq967"></a>
<h4 id="wq967">Example 3</h4>
<p>Make the same query as in example 2, only use UNION ALL so that no duplicate
rows are eliminated. </p>
<pre class="xmp"> <span class="bold">SELECT</span> EMPNO <span class="bold">FROM</span> EMPLOYEE
<span class="bold">WHERE</span> WORKDEPT <span class="bold">LIKE</span> 'E%'
<span class="bold">UNION ALL</span>
<span class="bold">SELECT</span> EMPNO FROM EMPPROJACT
<span class="bold">WHERE</span> PROJNO <span class="bold">IN(</span>'MA2100', 'MA2110', 'MA2112'<span class="bold">)</span></pre>
<a name="wq968"></a>
<h4 id="wq968">Example 4</h4>
<p>Make the same query as in example 2, and, in addition, "tag" the rows from
the EMPLOYEE table with 'emp' and the rows from the EMPPROJACT table with
'empprojact'. Unlike the result from example 2, this query may return the
same EMPNO more than once, identifying which table it came from by the associated
"tag".</p>
<pre class="xmp"> <span class="bold">SELECT</span> EMPNO, 'emp' <span class="bold">FROM</span> EMPLOYEE
<span class="bold">WHERE</span> WORKDEPT <span class="bold">LIKE</span> 'E%'
<span class="bold">UNION</span>
<span class="bold">SELECT</span> EMPNO, 'empprojact' <span class="bold">FROM</span> EMPPROJACT
<span class="bold">WHERE</span> PROJNO <span class="bold">IN(</span>'MA2100', 'MA2110', 'MA2112'<span class="bold">)</span></pre>
<a name="wq969"></a>
<h4 id="wq969">Example 5</h4>
<p>This example of EXCEPT produces all rows that are in T1 but not in T2,
with duplicate rows removed.</p>
<pre class="xmp"> <span class="bold">(SELECT</span> * <span class="bold">FROM</span> T1<span class="bold">)</span>
<span class="bold">EXCEPT DISTINCT</span>
<span class="bold">(SELECT</span> * <span class="bold">FROM</span> T2<span class="bold">)</span>
</pre><p class="indatacontent">If no NULL values are involved, this example returns the same results
as:</p>
<pre class="xmp"> <span class="bold">(SELECT DISTINCT</span> *
<span class="bold">FROM</span> T1
<span class="bold">WHERE NOT EXISTS</span> <span class="bold">(SELECT</span> * <span class="bold">FROM</span> T2
<span class="bold">WHERE</span> T1.C1 = T2.C1 <span class="bold">AND</span> T1.C2 = T2.C2 <span class="bold">AND</span>...<span class="bold">) )</span>
</pre><p class="indatacontent">where C1, C2, and so on represent the columns of T1 and T2.</p>
<a name="wq970"></a>
<h4 id="wq970">Example 6</h4>
<p>This example of INTERSECT produces all rows that are in both tables T1
and T2, with duplicate rows removed.</p>
<pre class="xmp"> <span class="bold">(SELECT</span> * <span class="bold">FROM</span> T1<span class="bold">)</span>
<span class="bold">INTERSECT DISTINCT</span>
<span class="bold">(SELECT</span> * <span class="bold">FROM</span> T2<span class="bold">)</span>
</pre><p class="indatacontent">If no NULL values are involved, this example returns the same results
as:</p>
<pre class="xmp"> <span class="bold">(SELECT DISTINCT</span> *
<span class="bold">FROM</span> T1
<span class="bold">WHERE EXISTS</span> <span class="bold">(SELECT</span> * <span class="bold">FROM</span> T2
<span class="bold">WHERE</span> T1.C1 = T2.C1 <span class="bold">AND</span> T1.C2 = T2.C2 <span class="bold">AND</span>...<span class="bold">) )</span>
</pre><p class="indatacontent">where C1, C2, and so on represent the columns of T1 and T2.</p>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstsubselect.htm">Previous Page</a> | <a href="rbafzmstintsel.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>
Reference in New Issue View Git Blame Copy Permalink