Wednesday, 27 February 2013

(+) to LEFT/RIGHT OUTER JOIN

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the Oracle notation for outer joins into a "LEFT OUTER JOIN" and "RIGHT OUTER JOIN".

(+) is an Oracle specific function[1].

Let's go with the old and trivial example of the employees and the departments in a company, before we dive any deeper. It seems to work for everyone else, so it should work here.

As we see if the (+) is on the right of the expression, it is a "LEFT OUTER JOIN" and vice versa.

The example comes straight from [3]. It will show NULL for the department values for all employees that are not assigned to a department. Wikipedia has a nice article on the behaviour of outer joins.[4]

Multiple Outer Join


This is an example of how you could use left join multiple times.
In my work, what I usually find, is a query that selects from a master table that needs to provide all records, left joined with the rest of the world, just in case the records in other tables do not exist.

This won't work


You can write conditions in the "ON" clause, however, these conditions must be related to the SLAVE table. The following, for example, will not work.[2]

It will show all employees, even the ones that are no longer working for the company.

The solution here is to move the offending condition into the WHERE clause.

Odd one out

The example below, I encountered, is a bit odd, as it is not really an outer join between tables. It is used here to prevent having to write the 'or is null' part.

References

[1] Oracle (+)
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
[2] The ON condition
http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html>
[3] Example Oracle Left Outer Join
http://www.dba-oracle.com/tips_oracle_left_outer_join.htm
[4] Wikipedia JOIN SQL
http://en.wikipedia.org/wiki/Join_%28SQL%29
Outer joins in Oracle
https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle
A Visual Explanation of SQL Joins
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

1 comment: