Outer Joins


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.

Pre-Oracle9i Outer Join 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.

Tip

When you’re not sure where the outer join operator (+) goes, place it next to the table that is missing rows. In other words, rows need to be "added" to this table for the join to succeed in a regular equijoin.

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

Oracle9i Outer Join Syntax

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 that 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 a 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 a LEFT OUTER JOIN or a 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 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 looking 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.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net