| 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. |
|
|
