Oracle Nonequijoins and Outer Joins

Objectives

This lesson covers the following objectives:

• Construct and execute a SELECT statement to access data from more than one table using a nonequijoin
• Create and execute a SELECT statement to access data from more than one table using an Oracle outer join

Purpose

• What happens if you want to retrieve data from a table that has no corresponding column in another table?
• For instance, your math percentage grade of 92 is stored in the GRADES column in one table; the letter grade is stored in the LETTER_GRADE column in another table.
• How can we join the number grade with the letter grade?
• When data is recorded using a range, retrieving it is the job of a nonequijoin.
• The Oracle joins you’ve studied so far returned rows that had a matching value in both tables.
• Those rows that didn’t satisfy these conditions were just left out.
• Sometimes, however, you want all the data from one of the tables to be returned even if no data matches in the other table.
• In this lesson will also look at the Oracle Outer Joins to solve this issue.

Nonequijoin

• Example:
  • Suppose we want to know the grade_level for each employee’s salary.
  • The job_grades table does not have a common column with the employees table.
  • Using a nonequijoin allows us to join the two tables.

• Since there is no exact match between the two columns in each table, the equality operator = can’t be used.
• Although comparison conditions such as <= and >= can be used, BETWEEN…AND is a more effective way to execute a nonequijoin.
• A nonequijoin is equivalent to an ANSI JOIN ON (where the condition used is something other than equals).

SELECT last_name, salary, grade_level, lowest_sal, highest_sal
FROM employees, job_grades
WHERE (salary BETWEEN lowest_sal AND highest_sal);

Outer Join

• An outer join is used to see rows that have a corresponding value in another table plus those rows in one of the tables that have no matching value in the other table.
• To indicate which table may have missing data using Oracle Join Syntax, add a plus sign (+) after the table’s column name in the WHERE clause of the query.
• This query will return all employee last names, including those that are assigned to a department and those that are not.
• The same results could be obtained using an ANSI LEFT OUTER JOIN.

• This outer join would return all department IDs and department names, both those that have employees assigned to them and those that do not.
• The same results could be obtained using an ANSI RIGHT OUTER JOIN.

• It is not possible to have the equivalent of a FULL OUTER JOIN by adding a (+) sign to both columns in the join condition.
• Attempting this results in an error.

• The syntax variations of the outer join are shown.

Outer Join and ANSI equivalents

• The table below shows ANSI/ISO SQL: 99 joins and their equivalent Oracle outer joins.

Terminology

Key terms used in this tutorial included:

• Nonequijoin
• BETWEEN…AND
• Outer Joins

Summary

In this tutorial, you should have learned how to:

• Construct and execute a SELECT statement to access data from more than one table using a nonequijoin
• Create and execute a SELECT statement to access data from more than one table using an Oracle outer join