141 lines
7.7 KiB
HTML
141 lines
7.7 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 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>
|