(+) 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
No comments:
Post a Comment