Outer Joins

The outer join extends the result of an equi join or simple join. Outer join returns all the rows which satisfy the join condition and those rows from one table for that no rows from the other table satisfy the join condition.

In order to write a query which performs an outer join of table A and table B and records from A, the outer join operator (+) is applied to all columns of B in the join condition. The example provided below performs this operation.

Example

Let Consider  a  conditions  product  details  where  are  they  to  be  displayed  for  all  the  products ordered as well as not ordered in a single query.9
This can be done by using

SELECT a.pcode,b.ocode FROM product a, ordprd b WHERE a.pcode=b.pcode(+); # display the product code and table and order code from ordprd table#
The given query displays all the product codes from the product table and all the order codes from the ordprd table. Further to this, the product codes available in the Product table which do not exist in the ordprd table are also displayed. To get this, the (+) operator is used.

The Outer Joins are subject to subsequent restrictions:

•    Operator (+) can appear only in the WHERE clause and can be applied only to a column of a table or view.
•    If two tables are joined through multiple conditions, the (+) operator must appear in all these conditions.
•    The condition cannot use IN comparison operator to compare a column marked within the (+) operator to another expression.