<?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="copyright" content="(C) Copyright IBM Corporation 2005" /> <meta name="DC.rights.owner" content="(C) Copyright IBM Corporation 2005" /> <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="task" /> <meta name="DC.Title" content="Get information from multiple tables" /> <meta name="DC.Relation" scheme="URI" content="rzatcgetstartsql.htm" /> <meta name="DC.Relation" scheme="URI" content="../db2/rbafzmst02.htm" /> <meta name="DC.Format" content="XHTML" /> <meta name="DC.Identifier" content="rzatcmulttblsql" /> <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>Get information from multiple tables</title> </head> <body id="rzatcmulttblsql"><a name="rzatcmulttblsql"><!-- --></a> <!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script> <h1 class="topictitle1">Get information from multiple tables</h1> <div><div class="section">SQL allows you to get information from columns contained in more than one table. This operation is called a join operation. In SQL, a join operation is specified by placing the names of those tables you want to join together into the same FROM clause of a SELECT statement. <p>Suppose you want to see a list of all the suppliers and the item numbers and item names for their supplied items. The item name is not in the SUPPLIERS table. It is in the INVENTORY_LIST table. Using the common column, ITEM_NUMBER, you can see all three of the columns as if they were from a single table. </p> <p>Whenever the same column name exists in two or more tables being joined, the column name must be qualified by the table name to specify which column is really being referenced. In this SELECT statement, the column name ITEM_NUMBER is defined in both tables so the column name needs to be qualified by the table name. If the columns had different names, there is no confusion, so qualification is not needed.</p> </div> <ol><li class="stepexpand"><span>To perform this join, the following SELECT statement can be used. Enter it by typing it directly on the Enter SQL Statements display or by prompting. If using prompting, both table names need to be typed on the FROM tables input line.</span> <pre><strong>SELECT</strong> SUPPLIER_NUMBER, SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER, ITEM_NAME <strong>FROM</strong> SAMPLECOLL.SUPPLIERS, SAMPLECOLL.INVENTORY_LIST <strong>WHERE</strong> SAMPLECOLL.SUPPLIERS.ITEM_NUMBER = SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER</pre> </li> <li class="stepexpand"><span>Another way to enter the same statement is to use a correlation name. A correlation name provides another name for a table name to use in a statement. A correlation name must be used when the table names are the same. It can be specified following each table name in the FROM list. The previous statement can be rewritten as:</span> <pre><strong>SELECT</strong> SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME <strong>FROM</strong> SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y <strong>WHERE</strong> X.ITEM_NUMBER = Y.ITEM_NUMBER</pre> </li> </ol> <div class="section">In this example, SAMPLECOLL.SUPPLIERS is given a correlation name of X and SAMPLECOLL.INVENTORY_LIST is given a correlation name of Y. The names X and Y are then used to qualify the ITEM_NUMBER column name. <p>Running this example returns the following output:</p> <div class="p"><pre class="screen"> Display Data Data width . . . . . . : 45 Position to line . . . . . Shift to column . . . . . . ....+....1....+....2....+....3....+....4....+ SUPPLIER_NUMBER ITEM ITEM NUMBER NAME 1234 153047 Pencils, red 1234 229740 Lined tablets 1234 303476 Paper clips 9988 153047 Pencils, red 9988 559343 Envelopes, legal 2424 153047 Pencils, red 2424 303476 Paper clips 5546 775298 Chairs, secretary 3366 303476 Paper clips 3366 073956 Pens, black ******** End of data ******** F3=Exit F12=Cancel F19=Left F20=Right F21=Split</pre> </div> <div class="p"><div class="note"><span class="notetitle">Note:</span> Because no ORDER BY clause was specified for the query, the order of the rows returned by your query may be different.</div> </div> <p>The data values in the result table represent a composite of the data values contained in the two tables INVENTORY_LIST and SUPPLIERS. This result table contains the supplier number from the SUPPLIER table and the item number and item name from the INVENTORY_LIST table. Any item numbers that do not appear in the SUPPLIER table are not shown in this result table. The results are not guaranteed to be in any order unless the ORDER BY clause is specified for the SELECT statement. Because you did not change any column headings for the SUPPLIER table, the SUPPLIER_NUMBER column name is used as the column heading.</p> <div class="p">The following example shows how to use ORDER BY to guarantee the order of the rows. The statement first sorts the result table by the SUPPLIER_NUMBER column. Rows with the same value for SUPPLIER_NUMBER are sorted by their ITEM_NUMBER.<pre><strong>SELECT</strong> SUPPLIER_NUMBER,Y.ITEM_NUMBER,ITEM_NAME <strong>FROM</strong> SAMPLECOLL.SUPPLIERS X,SAMPLECOLL.INVENTORY_LIST Y <strong>WHERE</strong> X.ITEM_NUMBER = Y.ITEM_NUMBER <strong>ORDER BY</strong> SUPPLIER_NUMBER,Y.ITEM_NUMBER</pre> </div> <p>Running the previous statement produces the following output.</p> <div class="p"><pre class="screen"> Display Data Data width . . . . . . : 45 Position to line . . . . . Shift to column . . . . . . ....+....1....+....2....+....3....+....4....+ SUPPLIER_NUMBER ITEM ITEM NUMBER NAME 1234 153047 Pencils, red 1234 229740 Lined tablets 1234 303476 Paper clips 2424 153047 Pencils, red 2424 303476 Paper clips 3366 073956 Pens, black 3366 303476 Paper clips 5546 775298 Chairs, secretary 9988 153047 Pencils, red 9988 559343 Envelopes, legal ******** End of data ******** F3=Exit F12=Cancel F19=Left F20=Right F21=Split</pre> </div> </div> </div> <div> <div class="familylinks"> <div class="parentlink"><strong>Parent topic:</strong> <a href="rzatcgetstartsql.htm" title="This topic describes how to create and work with schemas, tables, and views using SQL statements in interactive SQL.">Get started with SQL</a></div> </div> <div class="relinfo"><strong>Related information</strong><br /> <div><a href="../db2/rbafzmst02.htm">SQL Reference</a></div> </div> </div> </body> </html>