Sometimes you want to join two tables and return all the rows in one table whether or not the second table contains a match on the join condition. This is known as performing an outer join between two tables. To illustrate why you would want to join two tables in this way, consider the EMPLOYEES and DEPARTMENTS tables for Scott's widget company. The EMPLOYEES table has a column called DEPARTMENT_ID, which can contain NULL values. If you were to join the two tables on the DEPARTMENT_ID column, the query would not return all employees. Conversely, if you had departments that did not have any employees, you would not see all of the departments represented in the query results either.
outer join
A join between two or more tables returning all the rows in one table whether or not the second table contains a match on the join condition.
In some cases, you want to see all records in both tables, regardless of how many match on the join condition. This is known as a full outer join.
Let's look at how to perform these types of outer joins using the pre-Oracle9i syntax and the Oracle9i syntax.
The key component of the outer join syntax for previous Oracle versions is a plus sign enclosed in parentheses: (+). In an outer join, this outer join operator is placed next to the table that may not have rows that satisfy the join condition between two tables. We'll look at some examples in the next few sections, as Janice prepares some new reports.
Outer Join
King wants Janice to produce a report listing the sales representatives and the departments in which they reside. Janice knows that at any given time, there might be employees who aren't assigned to a department. She constructs the query assuming that there might be some missing or incorrect department numbers in the EMPLOYEES table:
select e.employee_id"Emp ID", e.last_name || ', ' || e.first_name"Name", d.department_name"Dept" from employees e,departments d where e.department_id = d.department_id(+) and e.job_id = 'sA_REP'; Emp ID Name Dept ---------- -------------------------- -------------------- 179 Johnson, Charles Sales 177 Livingston, Jack Sales 176 Taylor, Jonathon Sales 175 Hutton, Alyssa Sales 174 Abel, Ellen Sales ... 152 Hall, Peter Sales 151 Bernstein, David Sales 150 Tucker, Peter Sales 178 Grant, Kimberely 30 rows selected.
It appears that all of the employees who have a sales position are assigned to the Sales department, except for Kimberely Grant. She has a NULL value for her department ID and therefore does not match any row in the DEPARTMENTS table.
Janice could also find out which departments don't have any employees by changing the outer join to specify the EMPLOYEES table as the table that might not have any rows corresponding to a DEPARTMENTS table row, like this:
select e.employee_id"Emp ID", e.last_name || ', ' || e.first_name"Name", d.department_name"Dept" from employees e,departments d where e.department_id(+) = d.department_id; Emp ID Name Dept ---------- -------------------------- -------------------- 100 King, Steven Executive
101 Kochhar, Neena Executive 102 De Haan, Lex Executive 103 Hunold, Alexander IT ... 202 Fay, Pat Marketing 203 Mavris, Susan Human Resources 204 Baer, Hermann Public Relations 205 Higgins, Shelley Accounting 206 Gietz, William Accounting , NOC , Manufacturing , Government Sales , IT Support , Benefits , Shareholder Services , Retail Sales , Control And Credit , Recruiting , Operations , Treasury , Payroll , Corporate Tax , Construction , Contracting , IT Helpdesk 122 rows selected.
The report includes all departments but leaves out any employees that have an invalid department number or have no department number assigned to them. Janice will be addressing this issue in the next section.
|
Full Outer Join
King has asked Janice to somehow combine both of the reports she just created into a single report that lists all employees and all departments, regardless of whether an employee is assigned to a department or a department has any employees. To accomplish this using the pre-Oracle9i syntax, Janice must use the UNION operator to combine two outer join queries. The UNION operator will combine the results of two outer join queries, removing duplicates found between the two queries. Her query looks like this:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e,departments d where e.department_id(+) = d.department_id union select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e,departments d where e.department_id = d.department_id(+); Emp ID Name Dept ---------- -------------------------- -------------------- 100 King, Steven Executive 101 Kochhar, Neena Executive 102 De Haan, Lex Executive 103 Hunold, Alexander IT 104 Ernst, Janice IT 105 Austin, David IT 106 Pataballa, Valli IT ... 176 Taylor, Jonathon Sales 177 Livingston, Jack Sales 178 Grant, Kimberely 179 Johnson, Charles Sales 180 Taylor, Winston Shipping 181 Fleaur, Jean Shipping ... , Payroll , Recruiting , Retail Sales , Shareholder Services , Treasury 123 rows selected.
Notice that this query returns a total of 123 rows, one more than the previous version of this query that performed an outer join with the DEPARTMENTS table as the primary table. This version picked up the extra row containing Kimberely Grant from the outer join between EMPLOYEES and DEPARTMENTS in the first half of the query above.
While the query does provide the desired results, the maintenance costs are higher on a query of this type, since any changes to the first SELECT statement most likely must be reflected in the second SELECT statement. The new outer join syntax in Oracle9i addresses this problem.
As with the equijoin syntax, the outer join syntax in Oracle9i moves the join logic from the WHERE clause to the FROM clause. Rather than using the slightly unintuitive (+) outer join operator to specify an outer join, Oracle9i uses LEFT OUTER JOIN ... ON or RIGHT OUTER JOIN ... ON between the two tables to be joined. The LEFT or RIGHT specifies which table has all rows retrieved, regardless of whether there is a match in the other table.
Left Outer Join
Janice is rewriting some of the queries she wrote back when their shop was running Oracle8i. Now that they're using Oracle9i, she wants to make sure she is leveraging the full power of Oracle9i's new features, not to mention the added benefits of more intuitive syntax. She starts with one of the queries for King that retrieved employees and corresponding departments:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e,departments d where e.department_id = d.department_id(+) and e.job_id = 'sA_REP';
She rewrites the query using a LEFT OUTER JOIN, since the EMPLOYEES table is already on the "left" side of the FROM clause:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e left outer join departments d on e.department_id = d.department_id where e.job_id = 'sA_REP'; Emp ID Name Dept ------- ------------------------- ---------------------- 179 Johnson, Charles Sales 177 Livingston, Jack Sales 176 Taylor, Jonathon Sales
175 Hutton, Alyssa Sales 174 Abel, Ellen Sales ... 152 Hall, Peter Sales 151 Bernstein, David Sales 150 Tucker, Peter Sales 178 Grant, Kimberely 30 rows selected.
Not surprisingly, she gets the same results as she did when the query used the pre-Oracle9i syntax. However, this form of the query is much cleaner because the join syntax is separate from the filter criterion (employees who are sales representatives). The query is also much easier to read.
Right Outer Join
Any left outer join can be turned into a right outer join by changing the order of the tables and changing LEFT OUTER JOIN to RIGHT OUTER JOIN. The query in the previous section can be rewritten as RIGHT OUTER JOIN as follows:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from departments d right outer join employees e on e.department_id = d.department_id where e.job_id = 'sA_REP'; Emp ID Name Dept ------- ------------------------- ---------------------- 179 Johnson, Charles Sales 177 Livingston, Jack Sales 176 Taylor, Jonathon Sales 175 Hutton, Alyssa Sales 174 Abel, Ellen Sales ... 152 Hall, Peter Sales 151 Bernstein, David Sales 150 Tucker, Peter Sales 178 Grant, Kimberely 30 rows selected.
Many times, whether to use LEFT OUTER JOIN or RIGHT OUTER JOIN is simply a matter of style. As you can see, the two previous queries read differently but produce the same results.
Full Outer Join
Speaking of style and readability, the syntax for a full outer join in Oracle9i is greatly simplified compared to how a full outer join is performed in previous versions of Oracle. Rather than performing a UNION operation between two distinct queries, the FULL OUTER JOIN clause is specified between the two tables to be joined.
Janice is cleaning up the rest of her queries to take advantage of the new syntax, and she starts with the UNION query she wrote to display all employees and all departments in a single query. Here is the original query:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e,departments d where e.department_id(+) = d.department_id union select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e,departments d where e.department_id = d.department_id(+);
In its new format, it ends up a lot shorter and a lot more readable:
select e.employee_id "Emp ID", e.last_name || ', ' || e.first_name "Name", d.department_name "Dept" from employees e full outer join departments d on e.department_id = d.department_id; Emp ID Name Dept ------- ------------------------- ---------------------- 200 Whalen, Jennifer Administration 202 Fay, Pat Marketing 201 Hartstein, Michael Marketing ... , Corporate Tax , Construction , Contracting , IT Helpdesk 123 rows selected.