Outer joins can be split into two types:
- Left outer joins
- Right outer joins
To understand the difference between left and right outer joins, consider the following syntax:
FROM table1, table2
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.
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