Left and Right Outer Joins : Outer Joins Left Right « Table Joins « Oracle PL/SQL Tutorial

Outer joins can be split into two types:

1. Left outer joins
2. Right outer joins

To understand the difference between left and right outer joins, consider the following syntax:

Assume the tables are to be joined on table1.column1 and table2.column2.

Assume table1 contains a row with a null value in column1.

To perform a left outer join, the WHERE clause is

WHERE table1.column1 = table2.column2 (+);

In a left outer join, the outer join operator is actually on the right of the equality operator.

Next, assume table2 contains a row with a null value in column2.

To perform a right outer join, you switch the position of the outer join operator to the left of the equality operator and the WHERE clause becomes WHERE table1.column1 (+) = table2.column2;

Depending on whether table1 and table2 both contain rows with null values, you get different results depending on whether you use a left or right outer join.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813