Outer Join in Oracle

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns, each from a different table. To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.

Note: You cannot specify LOB columns in the WHERE clause if the WHERE clause contains the join condition. The use of LOBs in WHERE clauses are also subject to other restrictions.

Outer Join:

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

·         To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

·         To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

·         To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

Left Outer Join

Option-1:

SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.NO_OF_EMP

  FROM DEPARTMENTS A,

       (SELECT DEPARTMENT_ID, COUNT(*) NO_OF_EMP

          FROM EMPLOYEES

         WHERE DEPARTMENT_ID IS NOT NULL

         GROUP BY DEPARTMENT_ID) B

 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);

Option-2:

SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.NO_OF_EMP

  FROM DEPARTMENTS A

  LEFT OUTER JOIN (SELECT DEPARTMENT_ID, COUNT(*) NO_OF_EMP

                     FROM EMPLOYEES

                    WHERE DEPARTMENT_ID IS NOT NULL

                    GROUP BY DEPARTMENT_ID) B

    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

Output: no of effected Rows: 27

Right Outer Join

Option-1:

SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.NO_OF_EMP

  FROM DEPARTMENTS A,

       (SELECT DEPARTMENT_ID, COUNT(*) NO_OF_EMP

          FROM EMPLOYEES

         WHERE DEPARTMENT_ID IS NOT NULL

         GROUP BY DEPARTMENT_ID) B

 WHERE A.DEPARTMENT_ID(+) = B.DEPARTMENT_ID;

Option-2:

SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.NO_OF_EMP

  FROM DEPARTMENTS A

 RIGHT OUTER JOIN (SELECT DEPARTMENT_ID, COUNT(*) NO_OF_EMP

                     FROM EMPLOYEES

                    WHERE DEPARTMENT_ID IS NOT NULL

                    GROUP BY DEPARTMENT_ID) B

    ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

     

Output: no of effected Rows: 11

Followings are remembered while writing a OUTER JOIN query:

·         The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.

·         A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.

·         A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

·         A WHERE condition cannot compare any column marked with the (+) operator with a sub query.