Oracle:Joining tables

Main Page >> Oracle and SQL >> Workbook >> Joining tables

Joining Tables Introduction

Sometimes information needs to be retrieved from more than one table. The relationships between rows in one table and rows in another are established by the values in certain corresponding columns (foreign key).

For example:

EMP						DEPT
EMPNO	ENAME	JOB	.....	DEPTNO		DEPTNO	DNAME	......
7499	ALLEN	SALES	.....	30		30	SALES	......
				join attributes

The table must have matching values in the join attributes to enable a join to take place. If there are no matching values, the tables will not join! For example, if 30 was missing from the DEPT table, then the employees from department 30 would not appear in any output that joins tables DEPT and EMP together. (A consequence of violating referential integrity!)

Basic SQL Structure For Joining Tables

The format when joining tables is:

SELECT some columns

FROM two or more tables

WHERE table1.col1 = table2.col2 [ AND table3.col3 = table4.col4 [AND ....]]

Note, two, or more tables can be joined in a SQL statement, but each join condition specifies the link between two tables only. If, for example, three tables appear in the FROM clause, there should normally be two join conditions.

Join Operators

Operator	Description
=	Equal
<>	Not equal
>	Greater than
>=	Greater than, or equal to
<	Less than
<=	Less than, or equal to
BETWEEN lower-value AND higher-value	A value between lower and higher
LIKE	Pattern matching

Example Join Queries

To find ALLEN's location:

SELECT ENAME, LOC  
FROM EMP E, DEPT D 
WHERE ENAME = 'ALLEN' AND E.DEPTNO = D.DEPTNO;

Note: when two columns from different tables have the same name (i.e., DEPTNO in this case), you must use the table name prefixes to clarify exactly which columns you mean. Here, aliases have been used for the table names, e.g., E represents EMP and D DEPT. In this case the aliases work as a shorthand notation for the relevant tables. In other cases, for example, when comparing a table to itself (see later), they are vital.

To list information about all the employees in Chicago (without aliases):

SELECT DEPT.DEPTNO, DNAME, LOC, ENAME, JOB  
FROM EMP, DEPT  
WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'CHICAGO';

The same query using table abbreviations/aliases:

SELECT D.*, ENAME, JOB 
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO AND LOC = 'CHICAGO';

Note that the abbreviations are defined in the FROM clause. Once defined they must be used in place of the table they are representing.

Columns can also be given aliases with the AS new_column_name clause. This should directly follow the column name in the SELECT clause. The new column name can not contains spaces, unless you use double quotes and can be a maximum of 10 characters.

Joining DEPT and EMP using table and column aliases:

SELECT D.DEPTNO, DNAME AS DEPT_NAME, JOB, ENAME AS "EMP Name" 
FROM DEPT D, EMP E  
WHERE D.DEPTNO = E.DEPTNO  
ORDER BY D.DEPTNO;

To find the salary grade of each employee:

SELECT GRADE, JOB, ENAME, SAL  
FROM SALGRADE, EMP  
WHERE SAL BETWEEN LOSAL AND HISAL 
ORDER BY GRADE, JOB;

This is an example of a BETWEEN join, where the SAL field of each row of the EMP table is "tested" against the LOSAL and HISAL values of each row of the SALGRADE table to find the associated salary grade.

SALGRADE GRADE LOSAL HISAL 1 700 1200 2 1201 1400 3 1401 2000

In SQL, a column cannot be compared to itself, however for some queries this may be necessary, e.g., it may be required to compare one salesman's commission against another. To achieve this table aliases must be used.

To find the name and department of salesmen who earn more commission than 'Ward':

SELECT  S.ENAME,  S.DEPTNO,  S.COMM,  
J.ENAME,  J.DEPTNO,  J.COMM  
FROM  EMP  S,  EMP  J  
WHERE  J.ENAME  =  'WARD'  
AND  S.JOB  =  'SALESMAN'  
AND  S.COMM  >  J.COMM;

To find the salary and jobs of employees who earn more than Jones:

SELECT X.ENAME,  X.SAL,  X.JOB, Y.ENAME,  Y.SAL,  Y.JOB 
FROM  EMP  X,  EMP  Y  
WHERE  X.SAL  >  Y.SAL  AND  Y.ENAME  =  'JONES';

The query should result in something similar to:

ENAME            SAL JOB       ENAME            SAL JOB
---------- --------- --------- ---------- --------- ---------
KING            5000 PRESIDENT JONES           2975 MANAGER
SCOTT           3000 ANALYST   JONES           2975 MANAGER
FORD            3000 ANALYST   JONES           2975 MANAGER