OUTER Join in Oracle

What is an OUTER join in Oracle?

A JOIN is a query that combines rows from two or more Tables, View or Materialized View. There are four Oracle proprietary joins, an OUTER join is one of them. An OUTER join returns all rows that satisfy the join condition and also returns non-matching rows from one table and for those non-matching rows other table returns null. It means it returns matching and non-matching rows from one table and from another table returns matching rows and null for non-matching rows.

Points of Concentration

• An OUTER join combines multi Tables, View or Materialized View to retrieve data.
• An OUTER join returns all rows that satisfy the join condition and also returns non-matching rows.
• Oracle provides an OUTER join operator (+) to perform an OUTER join on multiple Tables and returns all rows from one table and NULL from another table for non-matching rows.
• If the OUTER join operator ‘(+)’ appears with the column of table A, Oracle returns all rows of table B and table A returns NULL for all rows that have no matching row(s).
• The columns in the conditions need not be part of the SELECT list.

Syntax:

SELECT Table1.Column_1, Table2.Column_n FROM Table1, Table2 WHERE    Table1.Column_2 (+) = Table2.Column_2;

SELECT Table1.Column_1, Table2.Column_n FROM Table1, Table2 WHERE  Table1.Column_2 = Table2.Column_2 (+);

Description:

• Col_1/2/n: The column(s) or calculation as per your requirement.
• Table 1/2: As per your requirement
• WHERE: It’s mandatory to use the OUTER join operator.
• +: It is an OUTER join operator.

Example:

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno ORDER BY E.Deptno;

Output:

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) ORDER BY E.Deptno;

Output:

Explanation: In the first example, OUTER join operator appears in Emp table (Alias name is E) so it returns all matching rows from the table Emp but Dept table (Alias name is D) returns all matching rows with non-matching rows and for that non-matching rows Emp table returns null values. According to ANSI, it’s RIGHT OUTER join.

But in the second example, OUTER join operator (+) appears in Dept table (Alias name is D) and it returns only matching rows because all rows of Emp table matched with Dept table rows and there is no any unmatched row in Emp table. That’s why it returns only matched rows. According to ANSI, it’s LEFT OUTER join.

Rules and Restrictions

Before implementing Oracle OUTER Join, must know some important rules and restrictions of OUTER Join. Listed below:

• The OUTER join operator (+) can appear only in the WHERE clause.
• The OUTER join operator (+) can appear in the context of the left correlation in the FROM clause and can be applied only to a column of a Table or View.
• If ‘A’ and ‘B’ are joined by multiple join conditions, the OUTER join operator (+) must be applied in all those conditions.
• The OUTER join operator (+) can be applied only to a column, not to an arbitrary expression.
• The OR logical operator cannot be used to combine the other condition if a condition containing the OUTER join operator (+)
• Using an IN operator, the column cannot perform conditional compare with The OUTER join operator (+) with an expression.
• A condition cannot compare any column marked with the OUTER join operator (+) with a subquery.
• If the OUTER join operator (+) appears with a column, a condition cannot be applied on that column to compare with a subquery.

Implementations of Oracle OUTER join with Examples

In this section, we’ll see the implementation of Oracle OUTER join and its behavior. For that, we will use the below sample tables (Emp, Dept) with 14 and 8 records respectively to understand the Oracle OUTER join behavior.

SELECT * from Emp;

Output:

SELECT * from Dept;

Output:

• The OUTER join operator (+) with other clauses

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) ORDER BY E.Deptno (+);

Output:

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) GROUP BY E.Ename (+), D.Deptno (+),D.Dname (+);

Output:

In the above example, the SELECT statement returns an “invalid character” error message. WHY?

Because The OUTER join operator (+) can appear only in the WHERE clause but in the above example, the OUTER join operator (+) appears in the other clauses as well.

• The OUTER join for multi conditional query

SELECT E.Ename, D.Deptno, D.Dname, D.Loc FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno AND E.Deptno = 10 ORDER BY E.Deptno;

Output:

SELECT E.Ename, D.Deptno, D.Dname, D.Loc FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno AND E.Deptno (+) = 10 ORDER BY E.Deptno;</code?

Output:

The above two examples are multi conditional, in the above first example OUTER join operator (+) appears in the first condition only but in the second example, OUTER join operator (+) appears in both the conditions. And because of that results are different.

In the first example, the second condition (without (+) operator) behaves a normal condition that filters the rows and returns the rows that belong to Deptno 10.

But in the second example OUTER join operator (+) appears in all the conditions, it returns all rows (matched and unmatched) of Dept table and matched rows from the Emp table and nulls for the unmatched rows.

TIPS:

• NVL function can be used to fill the nulls for the unmatched rows.
• The LOB column(s) cannot be specified in the WHERE clause when the WHERE clause contains any JOINS

Conclusion

Oracle OUTER JOIN is a query that combines multi Tables, View or Materialized View to retrieve data. If you are looking for all records from one table and only matched rows from another table, Oracle OUTER join can be used because it returns matching and non-matching rows from one table and matching rows from another table and nulls for the unmatched row(s).

Recommended Articles

This is a guide to OUTER Join in Oracle. Here we discuss the introduction, Points of Concentration and Implementations of Oracle OUTER join with Examples. You can also go through our other related articles to learn more–

1. Oracle UNION ALL
2. Left Join in Oracle
3. Oracle Operators
4. ORDER BY in Oracle