| < 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.
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 > |
|