The old syntax of the outer
join is a bit different from that of the
inner join, because it includes a special operator called the
outer join operator.
The outer join operator is a plus sign enclosed in parentheses:
(+). This operator is used in a join condition in
the WHERE clause following a field name from the table that you wish
to be considered the optional table.
For example, to list all the departments even if they are not related
to any particular location, you can perform an outer join between the
department and the location
tables as shown in the following example:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+);
DEPT_ID NAME REGIONAL_GROUP
---------- -------------------- ------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES
40 OPERATIONS BOSTON
Notice the (+) operator following
l.location_id. That makes
location the optional table in this join, in the
sense that you want to display a row from the
department table, even though there exists no
corresponding row in the LOCATION table. A corresponding query using
the new join syntax is:
SELECT d.dept_id, d.name, l.regional_group
FROM department d LEFT OUTER JOIN location l
ON d.location_id = l.location_id;
In the new outer join syntax, the LEFT (or RIGHT) keyword corresponds
to the table from which you want all the rows. This example uses LEFT
to point to department, because we want all the rows from the
department table irrespective of whether there are
coresponding rows in the location table.
A.2.1 Restrictions on Old Outer Join Syntax
There are some rules and restrictions on how you can use the outer
join operator in a query. When you use the (+)
operator in a query, Oracle doesn't allow you to
perform certain other operations in the same query. We discuss these
restrictions and some of the workarounds in the following list:
The outer join operator can appear on only one side of an expression
in the join condition. You get an ORA-01468 error if you attempt to
use it on both sides. For example:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id (+) = l.location_id(+);
WHERE d.location_id (+) = l.location_id(+)
*
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table
What this means is that the outer join operation using the
(+) operator is unidirectional. You
can't perform a bidirectional outer join (known as a
full outer join) using the (+) operator.
 |
If you are attempting a full outer join by placing the
(+) operator on both sides in the join condition,
please refer to Section A.2.2, which follows this section.
|
|
If a join involves more than two tables, then one table
can't be outer joined with more than one other table
in the query. Consider the following tables:
DESC employee
Name Null? Type
----------------------------------------- -------- --------------
EMP_ID NOT NULL NUMBER(5)
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
DEPT_ID NOT NULL NUMBER(5)
MANAGER_EMP_ID NUMBER(5)
SALARY NUMBER(5)
HIRE_DATE DATE
JOB_ID NUMBER(3)
DESC job
Name Null? Type
------------------------------- -------- ----
JOB_ID NOT NULL NUMBER(3)
FUNCTION VARCHAR2(30)
DESC department
Name Null? Type
------------------------------- -------- ----
DEPT_ID NOT NULL NUMBER(5)
NAME VARCHAR2(20)
LOCATION_ID NUMBER(3)
If you want to list the job function and department name of all the
employees, and you want to include all the departments and jobs that
don't have any corresponding employees, you would
probably attempt to join the employee table with
the job and department tables,
and make both the joins outer joins. However, since one table
can't be outer-joined with more than one table you
get the following error:
SELECT e.lname, j.function, d.name
FROM employee e, job j, department d
WHERE e.job_id (+) = j.job_id
AND e.dept_id (+) = d.dept_id;
WHERE e.job_id (+) = j.job_id
*
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table
As a workaround, you can create a view with an outer join between two
tables, and then outer join the view to the third table:
CREATE VIEW v_emp_job
AS SELECT e.dept_id, e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) = j.job_id;
SELECT v.lname, v.function, d.name
FROM v_emp_job v, department d
WHERE v.dept_id (+) = d.dept_id;
Instead of creating a view, you can use an inline view to achieve the
same result:
SELECT v.lname, v.function, d.name
FROM (SELECT e.dept_id, e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) = j.job_id) v, department d
WHERE v.dept_id (+) = d.dept_id;
Inline views are discussed in Chapter 5.
A condition containing the (+) operator may not
use the IN operator. For example:
SELECT e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) IN (668, 670, 667);
WHERE e.job_id (+) IN (668, 670, 667)
*
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
An outer join condition containing the (+)
operator may not be combined with another condition using the OR
operator. For example:
SELECT e.lname, d.name
FROM employee e, department d
WHERE e.dept_id = d.dept_id (+)
OR d.dept_id = 10;
WHERE e.dept_id = d.dept_id (+)
*
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
A condition containing the (+) operator may not
involve a subquery. For example:
SELECT e.lname
FROM employee e
WHERE e.dept_id (+) =
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING');
(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING')
*
ERROR at line 4:
ORA-01799: a column may not be outer-joined to a subquery
As a workaround, you can use an inline view to achieve the desired
effect:
SELECT e.lname
FROM employee e,
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING') V
WHERE e.dept_id (+) = v.dept_id;
A.2.2 Full Outer Join Using the Old Syntax
In the previous section,
you saw that a full outer join using the (+)
operator is not allowed. A UNION of two SELECT statements is a
workaround for this problem. In the following example, the first
SELECT represents an outer join in which
department is the optional table. The second
SELECT has the location table as the optional
table. Between the two SELECTS, you get all locations and all
departments. The UNION operation eliminates duplicate rows, and the
result is a full outer join:
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id (+) = l.location_id
UNION
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+) ;
DEPT_ID NAME REGIONAL_GROUP
---------- -------------------- ----------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES
40 OPERATIONS BOSTON
CHICAGO
SAN FRANCISCO
6 rows selected.
As you can see, this UNION query includes
all the rows you
would expect to see in a full outer join. UNION queries are discussed
in Chapter 7.
|
Using the ANSI/ISO-compatible join syntax introduced in
Oracle9i Database you can
perform a full outer join in a much more straightforward way than
shown in the previous example. See Section 3.3.3 in
Chapter 3.
|
|
