Oracle today provides full support for ANSI standard joins. This week's tip is a quick discussion of the inner join (or equijoin), starting with our beloved Oracle specific syntax (still supported) and providing three different ways of implementing the same join using the ANSI standard syntax.

An inner join is an operation that combines rows from two tables by matching the values in a column in one table to the values of a corresponding column in the other table. Whenever the values match, then the requested columns from each table are combined into a single row in a result set. If, for a given row in one table no corresponding column value is found in the other table, then the row is not included in the result set.

As an example, let's look at an abbreviated version of the well know employee (emp) and department (dept) tables from the Oracle scott schema. The following rows exist in the two tables.

SELECT deptno, dname
  2  FROM dept
  3  ORDER BY deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

4 rows selected.

SELECT deptno, empno, ename
  2  FROM emp
  3* ORDER BY deptno, empno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES
        30       7499 ALLEN
        30       7521 WARD
                 8000 MAYHEW

6 rows selected.

The result set from an inner join of these two tables is based on the column deptno that exists in each table. There is no employee row containing department number 40, so that department does not exist n the result set. And employee 8000, Mr. Mayhew, was once in a department, but was mysteriously removed, so he is also not included.

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- ----------
        10 ACCOUNTING           7839 KING
        10 ACCOUNTING           7934 MILLER
        20 RESEARCH             7566 JONES
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD

5 rows selected.

Below are four different ways of implementing the inner join to produce that same result set. 

First, the Oracle specific syntax, used as long as I can remember. The tables to be joined are listed in the FROM clause, and the join criteria is a condition in the WHERE clause.

SELECT d.deptno, d.dname, e.empno, e.ename
FROM   dept d, emp e
WHERE  d.deptno = e.deptno
ORDER BY d.deptno, e.empno;

Next, the JOIN ON statement, the ANSI join syntax that most closely resembles the Oracle syntax The tables to be joined are shown around the JOIN keyword and the join criteria immediately follows the ON keyword:

SELECT d.deptno, d.dname, e.empno, e.ename
FROM   dept d JOIN emp e ON d.deptno = e.deptno
ORDER BY d.deptno, e.empno;

Third, the JOIN USING statement. Again, the tables to be joined are shown around the JOIN keyword. But the USING clause indicates a common column in the two tables that should be used in the join operation. An interesting note about the JOIN USING syntax is that the USING column truly becomes a common column between the two tables and therefore does not belong to either table. The impact on the statement below is that no alias (d. or e.) is used to indicate to which table the column deptno belongs, and in fact its usage will result in an error.

SELECT deptno, d.dname, e.empno, e.ename
FROM   dept d JOIN emp e USING (deptno)
ORDER BY deptno, e.empno;

Finally, the NATURAL JOIN. Again, the tables to be joined are shown around the JOIN keyword. But no indication is made of the columns used to join the two tables. The natural join matches columns in the joined tables that have the same name and executes the join operation using those columns. In this particular case, the result is the same as in the above examples. As in the JOIN USING syntax, the columns used in the join operation become common columns between the two tables. But this syntax can result in problems. For example, if at a later date a CREATEDATE column is added to each table, then that column would also be used as part of the join criteria, even though that most likely is not the intention. For that reason, I would avoid this syntax.

SELECT deptno, d.dname, e.empno, e.ename
FROM   dept d NATURAL JOIN emp e
ORDER BY deptno, e.empno;

Note: This tip was tested using Oracle9i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.