Skip to content Skip to main navigation Skip to footer

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.
Nonequijoin
  • 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);
Nonequijoin

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.
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.
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.
Outer Join resulting an error

It is possible to perform a full outer join using Set Operators. These are covered later in the course.

  • The syntax variations of the outer join are shown.
The syntax variations of the outer join

Outer Join and ANSI equivalents

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

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *