Remote DBA Plans
Remote DBA Service

Advanced Oracle SQL: Equijoin and Outer Join

Oracle Tips by Laurent Schneider

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced Oracle SQL Programming" by Rampant TechPress. The following is an excerpt from the book.

Joins

A join is used to select data from more than one table.

1. Equijoin

An equijoin is a join where keys of both tables are matched using the equal comparison operator:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e,
   DEPT d
WHERE
   e.DEPTNO=d.DEPTNO;

ENAME          DEPTNO LOC
---------- ---------- -------------
SMITH              20 DALLAS
ALLEN              30 CHICAGO
WARD               30 CHICAGO
JONES              20 DALLAS
MARTIN             30 CHICAGO
BLAKE              30 CHICAGO
CLARK              10 NEW YORK
SCOTT              20 DALLAS
KING               10 NEW YORK
TURNER             30 CHICAGO
ADAMS              20 DALLAS
JAMES              30 CHICAGO
FORD               20 DALLAS
MILLER             10 NEW YORK

----------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   280 |     6 (17)| 00:00:01 |
|   1 | MERGE JOIN                  |         |    14 |   280 |     6 (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|* 4 |   SORT JOIN                  |         |    14 |   126 |     4 (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

Both EMP and DEPT contain a reference to DEPTNO. Some columns from both tables are returned. An alternative syntax does exactly the same thing using the JOIN keyword. The first syntax is NATURAL JOIN.

SELECT
   e.ENAME,
   DEPTNO,
   d.LOC
FROM
   EMP e
NATURAL JOIN
DEPT d;

Note: When selecting from more than one table, the best practice is to prefix all columns. Not only does this improve readability, but it also helps Oracle to parse the query quicker.

The join columns are not defined. Natural join match all columns with the same name. In EMP and DEPT, there is only one column with the same name, and it is called DEPTNO.

The second syntax is JOIN with USING:

SELECT
   e.ENAME,
   DEPTNO,
   d.LOC
FROM
   EMP e
JOIN
   DEPT d
USING
(
   DEPTNO
);

The USING clause helps by specifying the join column. The join column cannot be prefixed.

The third syntax is JOIN with ON. It is the most robust syntax as it allows prefixing:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e
JOIN
   DEPT d
ON
   e.DEPTNO=d.DEPTNO;

The join columns are defined in the ON clause.

2. Outer join

A left or right outer join is a join where rows in one table that do not have a matching row in the second table are selected with null values for the unknown columns:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e,
   DEPT d
WHERE
   e.DEPTNO(+)=d.DEPTNO;

ENAME          DEPTNO LOC
---------- ---------- -------------
SMITH              20 DALLAS
ALLEN              30 CHICAGO
WARD               30 CHICAGO
JONES              20 DALLAS
MARTIN             30 CHICAGO
BLAKE              30 CHICAGO
CLARK              10 NEW YORK
SCOTT              20 DALLAS
KING              10 NEW YORK
TURNER             30 CHICAGO
ADAMS              20 DALLAS
JAMES              30 CHICAGO
FORD               20 DALLAS
MILLER             10 NEW YORK
                   40 BOSTON
----------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   280 |     6 (17)| 00:00:01 |
|   1 | MERGE JOIN OUTER            |         |    14 |   280 |     6 (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|* 4 |   SORT JOIN                  |         |    14 |   126 |     4 (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")

Or:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   EMP e
RIGHT JOIN
   DEPT d
ON
   e.DEPTNO=d.DEPTNO

Or:

SELECT
   e.ENAME,
   d.DEPTNO,
   d.LOC
FROM
   DEPT d
LEFT JOIN
   EMP e
ON
   e.DEPTNO=d.DEPTNO

The right/left outer join selects departments with or without employees. As there is no employee in department 40 yet, department 40 is returned with a NULL value for the ENAME column of EMP.