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