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