1 Oracle_Style_Outer_Join Oracle Rdb supports the SQL Database Language Standard syntax for performing outer join between two or more tables, namely the LEFT, RIGHT, and FULL OUTER JOIN clauses. Oracle Rdb also supports alternative syntax and semantics that conform to those available in Oracle RDMS SQL language to enhance the compatibility between these two products. The special operator (+) can be placed in the WHERE clause to instruct SQL to join tables using outer join semantics. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle Rdb returns NULL for any select list expressions containing columns of B. Outer join queries are subject to the following rules and restrictions: o The (+) operator can appear only in the WHERE clause and can be applied only to a column of a table or view. o If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle Rdb will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join. o The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator. o A condition containing the (+) operator cannot be combined with another condition using the OR logical operator. o A condition cannot use the IN comparison operator to compare a column marked with the (+) operator with an expression. o A condition cannot compare any column marked with the (+) operator with a subquery. If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle Rdb returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle Rdb will return only the results of a simple join. In a query that performs outer joins of more than two pairs of tables, a single table can be the NULL-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. 2 Outer_Join_Examples The examples in this section extend the results of this inner join (Equijoin) between EMP and DEPT tables. SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno = dept.deptno; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME King President 10 Accounting Blake Manager 30 Sales Clark Manager 10 Accounting Jones Manager 20 Research Ford Analyst 20 Research Smith Clerk 20 Research Allen Salesman 30 Sales Ward Salesman 30 Sales Martin Salesman 30 Sales Scott Analyst 20 Research Turner Salesman 30 Sales Adams Clerk 20 Research James Clerk 30 Sales Miller Clerk 10 Accounting 14 rows selected The following query uses an outer join to extend the results of this Equijoin example above: SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME King President 10 Accounting Clark Manager 10 Accounting Miller Clerk 10 Accounting Jones Manager 20 Research Ford Analyst 20 Research Smith Clerk 20 Research Scott Analyst 20 Research Adams Clerk 20 Research Blake Manager 30 Sales Allen Salesman 30 Sales Ward Salesman 30 Sales Martin Salesman 30 Sales Turner Salesman 30 Sales James Clerk 30 Sales NULL NULL 40 Operations 15 rows selected In this outer join, Oracle Rdb returns a row containing the OPERATIONS department even though no employees work in this department. Oracle Rdb returns NULL in the ENAME and JOB columns for this row. The join query in this example selects only departments that have employees. The following query uses an outer join to extend the results of the preceding example: SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno cont> AND job (+) = 'Clerk'; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME Miller Clerk 10 Accounting Smith Clerk 20 Research Adams Clerk 20 Research James Clerk 30 Sales NULL NULL 40 Operations 5 rows selected In this outer join, Oracle Rdb returns a row containing the OPERATIONS department even though no clerks work in this department. The (+) operator on the JOB column ensures that rows for which the JOB column is NULL are also returned. If this (+) were omitted, the row containing the OPERATIONS department would not be returned because its JOB value is not 'CLERK'. SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno cont> AND job = 'Clerk'; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME Miller Clerk 10 Accounting Smith Clerk 20 Research Adams Clerk 20 Research James Clerk 30 Sales 4 rows selected This example shows four outer join queries on the CUSTOMERS, ORDERS, LINEITEMS, and PARTS tables. These tables are shown here: SQL> SELECT custno, custname cont> FROM customers cont> ORDER BY custno; CUSTNO CUSTNAME 1 Angelic Co 2 Believable Co 3 Cables R Us 3 rows selected SQL> SQL> SELECT orderno, custno, orderdate cont> FROM orders cont> ORDER BY orderno; ORDERNO CUSTNO ORDERDATE 9001 1 1999-10-13 9002 2 1999-10-13 9003 1 1999-10-20 9004 1 1999-10-27 9005 2 1999-10-31 5 rows selected SQL> SQL> SELECT orderno, lineno, partno, quantity cont> FROM lineitems cont> ORDER BY orderno, lineno; ORDERNO LINENO PARTNO QUANTITY 9001 1 101 15 9001 2 102 10 9002 1 101 25 9002 2 103 50 9003 1 101 15 9004 1 102 10 9004 2 103 20 7 rows selected SQL> SQL> SELECT partno, partname cont> FROM parts cont> ORDER BY partno; PARTNO PARTNAME 101 X-Ray Screen 102 Yellow Bag 103 Zoot Suit 3 rows selected The customer Cables R Us has placed no orders, and order number 9005 has no line items. The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned: SQL> SELECT custname, orderdate cont> FROM customers, orders cont> WHERE customers.custno = orders.custno (+) cont> ORDER BY customers.custno, orders.orderdate; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE Angelic Co 1999-10-13 Angelic Co 1999-10-20 Angelic Co 1999-10-27 Believable Co 1999-10-13 Believable Co 1999-10-31 Cables R Us NULL 6 rows selected The following outer join builds on the result of the previous one by adding the LINEITEMS table to the FROM clause, columns from this table to the select list, and a join condition joining this table to the ORDERS table to the where_clause. This query joins the results of the previous query to the LINEITEMS table and returns all customers, the dates they placed orders, and the part number and quantity of each part they ordered. The first (+) operator serves the same purpose as in the previous query. The second (+) operator ensures that orders with no line items are also returned: SQL> SELECT custname, orderdate, partno, quantity cont> FROM customers, orders, lineitems cont> WHERE customers.custno = orders.custno (+) cont> AND orders.orderno = lineitems.orderno (+) cont> ORDER BY customers.custno, orders.orderdate, lineitems.partno; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE LINEITEMS.PARTNO LINEITEMS.QUANTITY Angelic Co 1999-10-13 101 15 Angelic Co 1999-10-13 102 10 Angelic Co 1999-10-20 101 15 Angelic Co 1999-10-27 102 10 Angelic Co 1999-10-27 103 20 Believable Co 1999-10-13 101 25 Believable Co 1999-10-13 103 50 Believable Co 1999-10-31 NULL NULL Cables R Us NULL NULL NULL 9 rows selected The following outer join builds on the result of the previous one by adding the PARTS table to the FROM clause, the PARTNAME column from this table to the select list, and a join condition joining this table to the LINEITEMS table to the where_clause. This query joins the results of the previous query to the PARTS table to return all customers, the dates they placed orders, and the quantity and name of each part they ordered. The first two (+) operators serve the same purposes as in the previous query. The third (+) operator ensures that rows with NULL part numbers are also returned: SQL> SELECT custname, orderdate, quantity, partname cont> FROM customers, orders, lineitems, parts cont> WHERE customers.custno = orders.custno (+) cont> AND orders.orderno = lineitems.orderno (+) cont> AND lineitems.partno = parts.partno (+) cont> ORDER BY customers.custno, orders.orderdate, parts.partno; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE LINEITEMS.QUANTITY PARTS.PARTNAME Angelic Co 1999-10-13 15 X-Ray Screen Angelic Co 1999-10-13 10 Yellow Bag Angelic Co 1999-10-20 15 X-Ray Screen Angelic Co 1999-10-27 10 Yellow Bag Angelic Co 1999-10-27 20 Zoot Suit Believable Co 1999-10-13 25 X-Ray Screen Believable Co 1999-10-13 50 Zoot Suit Believable Co 1999-10-31 NULL NULL Cables R Us NULL NULL NULL 9 rows selected 2 Oracle_Server_Predicate The following notes apply when you use the Oracle server predicate: o If tables A and B are joined by multiple join conditions, then the plus (+) operator must be used in all these conditions. o The plus operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the plus operator. o A condition containing the plus operator cannot be combined with another condition using the OR logical operator. o A condition cannot use the IN comparison operator to compare a column marked with the plus operator to another expression. o A condition cannot compare a column marked with the plus operator to a subquery. o If the WHERE clause contains a condition that compares a column from table B to a constant, then the plus operator must be applied to the column such that the rows from table A for which Oracle Rdb has generated NULLs for this column are returned. o In a query that performs outer joins of more than two pairs of tables, a single table can only be the null-generated table for one other table. For this reason, you cannot apply the plus operator to the column of table B in the join condition for tables A and B and the join condition for tables B and C.