Oracle:Basic Join

Main Page >> Oracle and SQL >> Workbook >> Joins >> Basic Joins

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.

This format will work on all database management systems. More recent DBMS versions will also support the ANSI format of joining tables.

If you omit the JOIN condition, you will end up with a Cartesian product. That means given 2 tables X and Y, for every row in table X will be matched with every row in table Y! So if table X has 5 rows and table Y has 100, that will return 500 rows.

For example, try the following:

SELECT * FROM DEPT, EMP;

DEPT and EMP are only small tables, so the results are not over large. If you attempted this with tables where there are 1000s of rows, not only will it take time for the database to generate the results, the output would probably be meaningless.

Hint: if your query returns more rows that you have in the largest table of the query, then that should give you a clue that you have not joined your tables properly.

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

Multiple table joins

When joining tables you are not limited to just two tables. As many tables as required can be accessed.

The important thing is that the all the join clauses are specified otherwise you will cause a Cartesian product! Generally, for N tables there are normally N-1 join clauses in the WHERE clause.

To list the employee name, location and project name:

SELECT ENAME, LOC, PNAME 
FROM EMP E, DEPT D, PROJ P, EMPPROJ EP  -- 4 tables
WHERE E.DEPTNO = D.DEPTNO   -- join between DEPT and EMP (1)
  AND E.EMPNO = EP.EMPNO    -- join between EMP and EMPPROJ (2) 
  AND P.PROJNO = EP.PROJNO  -- join between PROJ and EMPPROJ (3)
 ;

So here, there are 4 tables in the FROM clause and 3 join clauses. The dashes: -- allow you to add code to your SQL statements, but note the semi-colon needs to be on a line without a commment.

The exception to this rule would be if you had a composite primary key, which would mean you would have a composite foreign key also. All the columns taking part in the composite primary key would have to be matched to the related foreign key components.

For example:

SELECT EP.EMPNO, EP.PROJNO, EP.HOURS, CLAIMDATE, CLAIMTOTAL
FROM EMPPROJ EP, EMPPROJ_EXPENSES EPE 
WHERE EP.EMPNO = EPE.EMPNO
AND EP.PROJNO = EPE.PROJNO;

This will only return some rows if you have completed the Create table section and Exercise 3.6.

Next Step

Recursive joins.