Outer Joins - Oracle DBA

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:

Outer join

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:

Outer join

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

Full Outer Join

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:

She rewrites the query using a LEFT OUTER JOIN, since the EMPLOYEES table is already on the "left" side of the FROM clause:

Left Outer Join

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:

In its new format, it ends up a lot shorter and a lot more readable:

Full Outer Join

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics