The
University
of
Vermont

 

SQL Joins. 

 Whenever you are referencing columns from more than one table, you MUST MUST MUST tell
ORACLE which rows from table A go with which rows from table B. By default, RDBMSes assume
ALL rows from table A go with EACH row from table B. Hardly ever a meaningful result.
Try: SELECT ENAME, DNAME FROM EMP, DEPT;

Now try: SELECT ENAME, DNAME FROM EMP, DEPT
               WHERE EMP.DEPTNO = DEPT.DEPTNO;

? Why did we use put "EMP." and "DEPT." in front of "DEPTNO"?

================================================
Second pass:

Think of a multi-table SELECT.  The first thing we want the RDBMS to do is
associate the proper rows from each table.  We tell the database how to do this with
JOIN conditions.  What we are asking the database to do is create a single "composite"
table with a bunch of columns taken from several diferent tables. Once the database has this
composite table built, it is easy for us to say which columns we want from which rows.

So, SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

EMP= 
 
Empno Ename Job Mgr Hire Sal Comm Deptno
Deptno Dname Loc

SELECT * FROM EMP, DEPT =
 
 
Empno Ename Job Mgr Hire Sal Comm Deptno Deptno Dname Loc

SELF JOIN
Sometimes you need to relate information from more than one row in the same table.
In these cases, you treat the table as if it were two, completely separate tables.
To do this you assign each table an alias.  The EMP table is the perfect example.
Suppose you want to show the ENAME of an employee, and the ENAME of that
employee's supervisor?
 
 
Empno Ename Job Mgr Hiredate Sal Comm Deptno
1 Keith 2
2 Roger 3

FROM EMP A, EMP B
 

SELECT A.ENAME, B.ENAME 
FROM EMP A, EMP B
WHERE B.EMPNO = A.MGR
 

Outer Join
If you join two tables with a conditional (= > <...),
and there is no matching row in the second table,
NO DATA IS RETURNED.  This is sometimes a problem.

For example:
Produce a report listing:  Employee's name, their supervisors name, their salary.

SELECT A.ENAME, B.ENAME, A.SAL
FROM EMP A, EMP B
WHERE B.EMPNO = A.MGR;

'KING's record doesn't show because she has no supervisor.

Try:
SELECT A.ENAME, B.ENAME, A.SAL
FROM EMP A, EMP B
WHERE B.EMPNO (+) = A.MGR;

Think of it as (+)= Fill this row in for me with nulls if it is missing.

BTW: This is an area of SQL that is less common between different dialects
of SQL. Some don't support it at all.
 
 
 

 
Brought to you through the courtesy of Computing and Information Technology, University of Vermont. Copyright © 1996 The University of Vermont and others. All rights reserved.

The University supports both institutional and personal web pages. The views expressed on personal web pages are strictly those of the author, and are not reviewed or approved by the University of Vermont

Send questions and comments to  Keith.Kennedy@uvm.edu

Read the Webmaster's Policies.
Last page update: November 11, 1998