Understanding Join Conditions and Join Types


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 .

Understanding Non-equijoins

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 

Understanding Outer Joins

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; 

Left and Right Outer Joins

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 

Limitations on Outer Joins

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.

Understanding Self Joins

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 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net