5.12 Outer join

< Day Day Up >

Both Oracle and DB2 support outer join. DB2 UDB supports the ANSI SQL syntax for three types of outer join: right, left, and full. Oracle supports the same syntax, starting in version 9i. Oracle also has proprietary left and right outer join syntax that DB2 does not support. Table 5-4 demonstrates how to map this old syntax to the DB2 equivalent for simple examples.

Table 5-4: Mapping of join definition
Oracle	DB2 UDB
SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id (+) = B.sales_rep_id;	SELECT A.last_name,A.id,B.name FROM emp A RIGHT OUTER JOIN customer B ON A.id = B.sales_rep_id;
SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id = B.sales_rep_id (+);	SELECT A.last_name,A.id,B.name FROM emp A LEFT OUTER JOIN customer B ON A.id = B.sales_rep_id;
SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id (+) = B.sales_rep_id (+);	SELECT A.last_name,A.id,B.name FROM emp A FULL OUTER JOIN customer B ON A.id = B.sales_rep_id;

Table 5-4: Mapping of join definition

Oracle

DB2 UDB

 SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id (+) = B.sales_rep_id;

 SELECT A.last_name,A.id,B.name FROM emp A RIGHT OUTER JOIN customer B ON A.id = B.sales_rep_id;

 SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id = B.sales_rep_id (+);

 SELECT A.last_name,A.id,B.name FROM emp A LEFT OUTER JOIN customer B ON A.id = B.sales_rep_id;

 SELECT A.last_name, A.id,B.name FROM emp A, Customer B WHERE A.id (+) = B.sales_rep_id (+);

 SELECT A.last_name,A.id,B.name FROM emp A FULL OUTER JOIN customer B ON A.id = B.sales_rep_id;

The MTK provides basic support for Oracle outer joins, with the following restrictions:

Only the equality (=) operator is supported.
The (+) operator cannot follow a complex expression; it can follow a column reference only.

In some cases, the MTK will not be able to convert complex outer join syntax. The following example shows how a complex SQL statement involving multiple outer joins can be mapped from Oracle to DB2 syntax.

It is important to realize that in Oracle, outer joins are defined in the WHERE clause, whereas in DB2 they are defined in the FROM clause. Further, the outer join condition of the two tables must be specified in the ON clause, not in the WHERE clause.

Example 5-34 shows the Oracle outer join syntax:

Example 5-34: Oracle outer joins

 SELECT    t1.surname FROM    EXAMPLE_TABLE1 t1,    EXAMPLE_TABLE2 t2,    EXAMPLE_TABLE3 t3,    EXAMPLE_TABLE4 t4 WHERE       ((t1.emptype = 1) OR (t1.position = 'Manager'))    AND (t1.empid = t2.empid(+))    AND (t2.empid = t3.empid(+))    AND (t2.sin = t3.sin(+))    AND (t3.jobtype(+) = 'Full-Time')    AND (t2.empid = t4.empid(+))    AND (t2.sin = t4.sin(+)) ORDER BY    t1.emptype, t2.other

Example 5-35 shows the DB2 conversion:

Example 5-35: DB2 outer join conversion

 SELECT    t1.surname, FROM       EXAMPLE_TABLE1 t1 LEFT OUTER JOIN         EXAMPLE_TABLE2 t2 ON (t2.empid = t1.empid) LEFT OUTER JOIN           EXAMPLE_TABLE3 t3 ON (t3.sin = t2.sin)                                   AND (t3.empid = t2.empid)                                   AND (t3.jobtype = 'Full-Time')                                LEFT OUTER JOIN             EXAMPLE_TABLE4 t4 ON (t4.sin = t2.sin)                                   AND (t4.empid = t2.empid) WHERE    ((t1.emptype = 1) OR (t1.position = 'Manager')) ORDER BY    t1.emptype, t2.other

< Day Day Up >