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
0 Comments