Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting009.htm

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting009.htm

<!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" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-04-21T9:46:23Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Selecting Data from Multiple Tables" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10766-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdddg_selecting008.htm" title="Previous" type="text/html" />
<link rel="next" href="tdddg_selecting010.htm" title="Next" type="text/html" />
<title>Selecting Data from Multiple Tables</title>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdddg_selecting008.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting010.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="CEGBDEJC" name="CEGBDEJC"></a><a id="TDDDG99961" name="TDDDG99961"></a></p>
<div class="sect1">
<h1>Selecting Data from Multiple Tables</h1>
<a name="BEGIN" id="BEGIN"></a>
<p>Suppose that you want to select the <code>FIRST_NAME</code>, <code>LAST_NAME</code>, and <code>DEPARTMENT_NAME</code> of every employee. <code>FIRST_NAME</code> and <code>LAST_NAME</code> are in the <code>EMPLOYEES</code> table, and <code>DEPARTMENT_NAME</code> is in the <code>DEPARTMENTS</code> table. Both tables have <code>DEPARTMENT_ID</code>. You can use the query in the following example. Such a query is called a <span class="bold">join</span>.</p>
<div class="example"><a id="BCGGHIAB" name="BCGGHIAB"></a><a id="TDDDG145" name="TDDDG145"></a>
<p class="titleinexample">Selecting Data from Two Tables (Joining Two Tables)</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
<span class="bold">WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID</span>;
 
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Jennifer             Whalen                    Administration
Michael              Hartstein                 Marketing
Pat                  Fay                       Marketing
Den                  Raphaely                  Purchasing
Karen                Colmenares                Purchasing
Alexander            Khoo                      Purchasing
Shelli               Baida                     Purchasing
Sigal                Tobias                    Purchasing
Guy                  Himuro                    Purchasing
Susan                Mavris                    Human Resources
Donald               OConnell                  Shipping
 
First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Douglas              Grant                     Shipping
...
Shelley              Higgins                   Accounting
 
106 rows selected.
</pre></div>

<p>Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in the preceding example:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT <span class="bold">FIRST_NAME</span> "First",
<span class="bold">LAST_NAME</span> "Last",
<span class="bold">DEPARTMENT_NAME</span> "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE <span class="bold">EMPLOYEES.DEPARTMENT_ID</span> = <span class="bold">DEPARTMENTS.DEPARTMENT_ID</span>;
</pre>
<p><a id="sthref122" name="sthref122"></a><a id="sthref123" name="sthref123"></a><a id="sthref124" name="sthref124"></a>To make queries that use qualified column names more readable, use aliases, as in the following example:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM <span class="bold">EMPLOYEES e</span>, <span class="bold">DEPARTMENTS d</span>
WHERE <span class="bold">e.DEPARTMENT_ID</span> = d.DEPARTMENT_ID;
</pre>
<p>Although you create the aliases in the <code>FROM</code> clause, you can use them earlier in the query, as in the following example:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT <span class="bold">e.FIRST_NAME</span> "First",
<span class="bold">e.LAST_NAME</span> "Last",
<span class="bold">d.DEPARTMENT_NAME</span> "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
</pre>
<div class="helpinfonotealso">
<h2>Related Topics</h2>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF30046','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a></p>
<p><a href="tdddg_selecting.htm#CHDHHAFA">Selecting Table Data</a></p>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdddg_selecting008.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting010.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de