 |
|
The Equi-Join
Oracle Tips by Burleson Consulting |
An equi-join is any SQL statement that
references two or more tables, with an equality predicate in the
where clause to specify the join condition for the tables
(Figure 16-1).
Figure 1: The SQL
equi-join
The equi-join is the most common of all of
the join types and therefore deserves a closer inspection. For
example, what follows is an equi-join to display all employees and
their bonuses:
select
emp.ename,
emp.deptno
bonus.comm
from
emp,
bonus
where
emp.ename = bonus.ename
;
Here we see the output from this query. Note
that the equi-join key (ename in this example) does not need
to be displayed in the result set to serve as the join key.
ENAME
DEPTNO COMM
---------- ---------- ----------
ALLEN
30 300
WARD
30 500
MARTIN
30 1400
The equi-join is the most straightforward of
all of the relational join operators, and Oracle offers three join
methods for equi-joins, the nested loop join method, the hash join
method, and the sort merge join method.
The Outer Join
An outer join is a special case of a
table join where unmatched columns from a table are still displayed
in the output of the query (Figure 16-2).
Figure 2: An outer
join
The outer join is implemented by placing the
plus-sign (+) operator in the equality predicate of the
where clause. In the next example, we want to display all
employees, not just those who received a bonus. Hence, we place the
(+) outer join directive on the side of the equality that references
the bonus table to indicate that we also want the non-matching rows.
select
emp.ename,
emp.deptno,
bonus.comm
from
emp,
bonus
where
emp.ename = bonus.ename(+)
;
Let’s examine the output from this query. As you can
see, the (+) directive made the Oracle SQL include emp rows,
even where there was no matching row in the bonus table.
ENAME
DEPTNO COMM
---------- ---------- ----------
ALLEN
30 300
WARD
30 500
MARTIN
30 1400
FORD
20
SCOTT
20
JAMES
30
KING
10
BLAKE
30
MILLER
10
TURNER
30
CLARK
10
JONES
20
ADAMS 20
SMITH
20
This is first_rows execution plan for this query. Note
the use of the NESTED LOOPS OUTER access method. We must also note
that the SQL optimizer understands that we want to see all of the
rows in the emp table, and it has wisely chosen a full-table
scan because all of the rows are required.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
NESTED LOOPS
OUTER
1
TABLE ACCESS
FULL
EMP
1
TABLE ACCESS
BY INDEX ROWID
BONUS
2
INDEX
RANGE SCAN
BONUS_ENAME
1
In this case of an outer join, the RBO and
the CBO will always generate an identical table access method. The
only difference is that the position of the tables in the where
clause will affect the choice of the driving table in the RBO, while
the CBO will generally use the table with the smallest value for
num_rows as the driving table.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
