<?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 lang="en-us" xml:lang="en-us"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <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="DC.Type" content="reference" /> <meta name="DC.Title" content="Use UNION keyword to combine subselects" /> <meta name="abstract" content="Using the UNION keyword, you can combine two or more subselects to form a fullselect." /> <meta name="description" content="Using the UNION keyword, you can combine two or more subselects to form a fullselect." /> <meta name="DC.subject" content="SELECT statement, UNION, UNION keyword, keyword, examples" /> <meta name="keywords" content="SELECT statement, UNION, UNION keyword, keyword, examples" /> <meta name="DC.Relation" scheme="URI" content="rbafytexas.htm" /> <meta name="DC.Relation" scheme="URI" content="rbafykeyuall.htm" /> <meta name="DC.Relation" scheme="URI" content="rbafyviewnt.htm" /> <meta name="DC.Relation" scheme="URI" content="rbafyussisql.htm" /> <meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" /> <meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" /> <meta name="DC.Format" content="XHTML" /> <meta name="DC.Identifier" content="rbafykeyu" /> <meta name="DC.Language" 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. --> <link rel="stylesheet" type="text/css" href="./ibmdita.css" /> <link rel="stylesheet" type="text/css" href="./ic.css" /> <title>Use UNION keyword to combine subselects</title> </head> <body id="rbafykeyu"><a name="rbafykeyu"><!-- --></a> <!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script> <h1 class="topictitle1">Use UNION keyword to combine subselects</h1> <div><p>Using the UNION keyword, you can combine two or more subselects to form a fullselect.</p> <div class="section"><p> When SQL encounters the UNION keyword, it processes each subselect to form an interim result table, then it combines the interim result table of each subselect and deletes duplicate rows to form a combined result table. You can use different clauses and techniques when coding select-statements.</p> </div> <div class="section"><p>You can use UNION to eliminate duplicates when merging lists of values obtained from several tables. For example, you can obtain a combined list of employee numbers that includes:</p> <ul><li>People in department D11</li> <li>People whose assignments include projects MA2112, MA2113, and AD3111</li> </ul> </div> <div class="section"><p>The combined list is derived from two tables and contains no duplicates. To do this, specify:</p> <pre><strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPLOYEE <strong>WHERE</strong> WORKDEPT = 'D11' <strong>UNION</strong> <strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPPROJACT <strong>WHERE</strong> PROJNO = 'MA2112' <strong>OR</strong> PROJNO = 'MA2113' <strong>OR</strong> PROJNO = 'AD3111' <strong>ORDER BY</strong> EMPNO</pre> </div> <div class="section"><p>To better understand the results from these SQL statements, imagine that SQL goes through the following process:</p> </div> <div class="section"><p>Step 1. SQL processes the first SELECT statement: </p> <pre><strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPLOYEE <strong>WHERE</strong> WORKDEPT = 'D11'</pre> <p>Which results in an interim result table:</p> <div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr valign="top"><th valign="top" id="d0e105">EMPNO from CORPDATA.EMPLOYEE</th> </tr> </thead> <tbody><tr><td valign="top" headers="d0e105 ">000060</td> </tr> <tr><td valign="top" headers="d0e105 ">000150</td> </tr> <tr><td valign="top" headers="d0e105 ">000160</td> </tr> <tr><td valign="top" headers="d0e105 ">000170</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">000180</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">000190</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">000200</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">000210</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">000220</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">200170</td> </tr> <tr valign="top"><td valign="top" headers="d0e105 ">200220</td> </tr> </tbody> </table> </div> <p>Step 2. SQL processes the second SELECT statement:</p> <pre><strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPPROJACT <strong>WHERE</strong> PROJNO='MA2112' <strong>OR</strong> PROJNO= 'MA2113' <strong>OR</strong> PROJNO= 'AD3111'</pre> <p>Which results in another interim result table:</p> <div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr valign="top"><th valign="top" id="d0e166">EMPNO from CORPDATA.EMPPROJACT</th> </tr> </thead> <tbody><tr><td valign="top" headers="d0e166 ">000230</td> </tr> <tr><td valign="top" headers="d0e166 ">000230</td> </tr> <tr><td valign="top" headers="d0e166 ">000240</td> </tr> <tr><td valign="top" headers="d0e166 ">000230</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000230</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000240</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000230</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000150</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000170</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000190</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000170</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000190</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000150</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000160</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000180</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000170</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000210</td> </tr> <tr valign="top"><td valign="top" headers="d0e166 ">000210</td> </tr> </tbody> </table> </div> <p>Step 3. SQL combines the two interim result tables, removes duplicate rows, and orders the result:</p> <pre><strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPLOYEE <strong>WHERE</strong> WORKDEPT = 'D11' <strong>UNION</strong> <strong>SELECT</strong> EMPNO <strong>FROM</strong> CORPDATA.EMPPROJACT <strong>WHERE</strong> PROJNO='MA2112' <strong>OR</strong> PROJNO= 'MA2113' <strong>OR</strong> PROJNO= 'AD3111' <strong>ORDER BY</strong> EMPNO</pre> <p>Which results in a combined result table with values in ascending sequence:</p> <div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr valign="top"><th valign="top" id="d0e263">EMPNO</th> </tr> </thead> <tbody><tr><td valign="top" headers="d0e263 ">000060</td> </tr> <tr><td valign="top" headers="d0e263 ">000150</td> </tr> <tr><td valign="top" headers="d0e263 ">000160</td> </tr> <tr><td valign="top" headers="d0e263 ">000170</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000180</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000190</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000200</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000210</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000220</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000230</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">000240</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">200170</td> </tr> <tr valign="top"><td valign="top" headers="d0e263 ">200220</td> </tr> </tbody> </table> </div> </div> <div class="section"><p>When you use UNION:</p> <ul><li>Any ORDER BY clause must appear after the last subselect that is part of the union. In this example, the results are sequenced on the basis of the first selected column, <em>EMPNO</em>. The ORDER BY clause specifies that the combined result table is to be in collated sequence. ORDER BY is not allowed in a view.</li> <li>A name may be specified on the ORDER BY clause if the result columns are named. A result column is named if the corresponding columns in each of the unioned select-statements have the same name. An AS clause can be used to assign a name to columns in the select list. <pre> <strong>SELECT</strong> A + B <strong>AS</strong> X ... <strong>UNION SELECT</strong> X ... <strong>ORDER BY</strong> X</pre> <div class="p">If the result columns are unnamed, use a positive integer to order the result. The number refers to the position of the expression in the list of expressions you include in your subselects. <pre> <strong>SELECT</strong> A + B ... <strong>UNION SELECT</strong> X ... <strong>ORDER BY</strong> 1</pre> </div> </li> </ul> </div> <div class="section"><p>To identify which subselect each row is from, you can include a constant at the end of the select list of each subselect in the union. When SQL returns your results, the last column contains the constant for the subselect that is the source of that row. For example, you can specify: </p> <pre> <strong>SELECT</strong> A, B, 'A1' ... <strong>UNION SELECT</strong> X, Y, 'B2'...</pre> </div> <div class="section"><p>When a row is returned, it includes a value (either A1 or B2) to indicate the table that is the source of the row's values. If the column names in the union are different, SQL uses the set of column names specified in the first subselect when interactive SQL displays or prints the results, or in the SQLDA resulting from processing an SQL DESCRIBE statement.</p> </div> <div class="section"><div class="p"> <div class="note"><span class="notetitle">Note:</span> Sort sequence is applied after the fields across the UNION pieces are made compatible. The sort sequence is used for the distinct processing that implicitly occurs during UNION processing. </div> </div> </div> </div> <div> <ul class="ullinks"> <li class="ulchildlink"><strong><a href="rbafykeyuall.htm">Specify UNION ALL</a></strong><br /> If you want to keep duplicates in the result of a UNION, specify UNION ALL instead of just UNION.</li> </ul> <div class="familylinks"> <div class="parentlink"><strong>Parent topic:</strong> <a href="rbafytexas.htm" title="Learn a variety of ways of tailoring your query to gather data using the SELECT statement. One way to do this is to use the SELECT statement in a program to retrieve a specific row (for example, the row for an employee). Furthermore, you can use clauses to gather data in a specific way.">Retrieve data using the SELECT statement</a></div> </div> <div class="relconcepts"><strong>Related concepts</strong><br /> <div><a href="rbafyussisql.htm" title="A sort sequence defines how characters in a character set relate to each other when they are compared or ordered. Normalization allows you to compare strings that contain combining characters.">Sort sequences and normalization in SQL</a></div> </div> <div class="relref"><strong>Related reference</strong><br /> <div><a href="rbafyviewnt.htm" title="A view can be used to access data in one or more tables or views. You create a view by using a SELECT statement.">Create and use views</a></div> </div> </div> </body> </html>