Earlier in this chapter, you saw that a table join consists of a column from a table, an operator, followed by another column. You also saw some simple examples that use joins. In this section, you ll explore join conditions and join types that allow you to create more advanced queries.
There are two types of join conditions , which are based on the operator you use in your join:
Equijoins You use the equality operator ( = ) in the join. You ve already seen examples of equijoins.
Non-equijoins You use an operator other than equals in the join, such as <, >, BETWEEN , and so on. You ll see examples of non-equijoins shortly.
In addition to the join condition, there are also three different types of joins:
Inner joins Return a row only when the columns in the join contain values that satisfy the join condition. This means that if a row has a null value in one of the columns in the join condition, that row isn t returned. The examples you ve seen so far have been inner joins.
Outer joins Can return a row even when one of the columns in the join condition contains a null value.
Self joins Return rows joined on the same table.
You ll learn about non-equijoins, outer joins, and self joins next .
A non- equijoin uses an operator other than the equality operator in the join. Examples of non-equality operators are not-equal (<>), less than (<), greater than (>), less than or equal to (< = ), greater than or equal to (> = ), LIKE , IN , and BETWEEN . The following example uses a non-equijoin to retrieve the salary grades for the employees , which is determined using the BETWEEN operator:
SELECT e.first_name, e.last_name, e.title, e.salary, sg.salary_grade_id FROM employees e, salary_grades sg WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary; FIRST_NAME LAST_NAME TITLE SALARY SALARY_GRADE_ID ---------- ---------- ------------------ ---------- --------------- Fred Hobbs Salesperson 150000 1 Susan Jones Salesperson 500000 2 Ron Johnson Sales Manager 600000 3 James Smith CEO 800000 4
An outer join retrieves a row even when one of the columns in the join contains a null value. You perform an outer join by supplying the outer join operator in the join condition; the outer join operator is a plus character in parentheses (+) .
Let s take a look at an example. Remember the query earlier that didn t show the My Front Line product because its product_type_id is null? You can use an outer join to get that row; notice that the outer join operator (+) is on the opposite side of the product_type_id column in the product table (this is the column that contains the null value):
SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id (+); NAME NAME ------------------------------ ---------- Modern Science Book Chemistry Book Supernova Video Tank War Video Z Files Video 2412: The Return Video Space Force 9 DVD From Another Planet DVD Classical Music CD Pop 3 CD Creative Yell CD My Front Line
Notice that My Front Line ”the product with the null product_type_id ”is now shown at the end.
Note | You can place the outer join operator on either side of the join operator, but you always keep it on the opposite side of the column that contains the null value for which you still want the row. |
The following query returns the same results as the previous one, but notice that the outer join operator is on the left of the equality operator in the join condition:
SELECT p.name, pt.name FROM products p, product_types pt WHERE pt.product_type_id (+) = p.product_type_id;
Outer joins can be split into two types:
Left outer joins
Right outer joins
To understand the difference between left and right outer joins, consider the following syntax:
SELECT ... FROM table1, table2 ...
Assume the tables are to be joined on table1.column1 and table2.column2 . Also, assume table1 contains a row with a null value in column1 . To perform a left outer join, the WHERE clause is
WHERE table1.column1 = table2.column2 (+);
Note | In a left outer join, the outer join operator is actually on the right of the equality operator. |
Next, assume table2 contains a row with a null value in column2 . To perform a right outer join, you switch the position of the outer join operator to the left of the equality operator and the WHERE clause becomes
WHERE table1.column1 (+) = table2.column2;
Note | As you ll see, depending on whether table1 and table2 both contain rows with null values, you get different results depending on whether you use a left or right outer join. |
Let s take a look at some concrete examples to make left and right outer joins clearer.
An Example of a Left Outer Join The following example shows the use of a left outer join; notice that the outer join operator appears on the right of the equality operator:
SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id (+); NAME NAME ------------------------------ ---------- Modern Science Book Chemistry Book Supernova Video Tank War Video Z Files Video 2412: The Return Video Space Force 9 DVD From Another Planet DVD Classical Music CD Pop 3 CD Creative Yell CD My Front Line
Notice all the rows from the products table are displayed in this example, including the My Front Line row that has a null value in the product_type_id column.
An Example of a Right Outer Join The product_types table contains a type of product not referenced in the products table (magazine); notice this product type at the end of the following listing:
SELECT * FROM product_types; PRODUCT_TYPE_ID NAME --------------- ---------- 1 Book 2 Video 3 DVD 4 CD 5 Magazine
You can see this product type by using a right outer join, as shown in the following example; notice that the outer join operator actually appears on the left of the equality operator:
SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id (+) = pt.product_type_id; NAME NAME ------------------------------ ---------- Modern Science Book Chemistry Book Supernova Video Tank War Video Z Files Video 2412: The Return Video Space Force 9 DVD From Another Planet DVD Classical Music CD Pop 3 CD Creative Yell CD Magazine
There are limitations on using outer joins, and you ll learn some of these limitations in this section.
You may only place the outer join operator on one side of the join (not both). If you try to place the outer join operator on both sides you get an error, as shown in the following example:
SQL> SELECT p.name, pt.name 2 FROM products p, product_types pt 3 WHERE p.product_type_id (+) = pt.product_type_id (+); WHERE p.product_type_id (+) = pt.product_type_id (+) * ERROR at line 3: ORA-01468: a predicate may reference only one outer-joined table
You cannot use an outer join condition with the IN operator:
SQL> SELECT p.name, pt.name 2 FROM products p, product_types pt 3 WHERE p.product_type_id (+) IN (1, 2, 3, 4); WHERE p.product_type_id (+) IN (1, 2, 3, 4) * ERROR at line 3: ORA-01719: outer join operator (+) not allowed in operand of OR or IN
You cannot use an outer join condition with another join using the OR operator:
SQL> SELECT p.name, pt.name 2 FROM products p, product_types pt 3 WHERE p.product_type_id (+) = pt.product_type_id 4 OR p.product_type_id = 1; WHERE p.product_type_id (+) = pt.product_type_id * ERROR at line 3: ORA-01719: outer join operator (+) not allowed in operand of OR or IN
Note | These are only a few of the limitations when using the outer join operator. For all the limitations, consult Oracle Corporation s SQL Reference manual. |
A self join is a join made on the same table. To perform a self join, you must use a different table alias to identify each reference of the table used in your query. Let s consider an example. The stor e schema contains a table named employees that contains a list of the employees. The manager _id column contains the employee_id of the manager for the employee (if that employee has a manager). The employees table contains the following rows:
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY ----------- ---------- ---------- ---------- ----------- -------- 1 James Smith CEO 800000 2 1 Ron Johnson Sales Manager 600000 3 2 Fred Hobbs Salesperson 150000 4 2 Susan Jones Salesperson 500000
As you can see, James Smith ”the CEO ”has a null value for the manager_id , meaning that he doesn t have a manager (he answers only to the shareholders). Fred Hobbs and Susan Jones both work for Ron Johnson.
You can use a self join to display the names of each employee and their manager. In the following example, the employees table is referenced twice, using two aliases w and m . The w alias is used to get the worker name, and the m alias is used to get the manager name. The self join is made between w.manager_id and m.employee_id :
SELECT w.first_name ' ' w.last_name ' works for ' m.first_name ' ' m.last_name FROM employees w, employees m WHERE w.manager_id = m.employee_id; W.FIRST_NAME''W.LAST_NAME'WORKSFOR'M.FIRST_NA ----------------------------------------------------- Ron Johnson works for James Smith Fred Hobbs works for Ron Johnson Susan Jones works for Ron Johnson
Since James Smith s manager_id is null ”he s the CEO ”no row is displayed for him.
You can of course perform outer joins in combination with self joins. In the following example, an outer join is used with the self join shown in the previous example so that you can see the row for James Smith. You ll notice the use of the NVL() function to provide a string indicating that James Smith works for the shareholders (remember, he s the CEO, so he reports to the shareholders of the company):
SELECT w.last_name ' works for ' NVL(m.last_name, 'the shareholders') FROM employees w, employees m WHERE w.manager_id = m.employee_id; W.LAST_NAME'WORKSFOR'NVL(M.LAST_N ------------------------------------- Smith works for the shareholders Johnson works for Smith Hobbs works for Johnson Jones works for Johnson