Re: Outer Join

BigLearner wrote:
> Hai buddies,
>
> Thanks for the replies.
>
> I guess now I can narrow down my question.
>
> I saw the link you gave.
>
> When the + symbol appears on the right, its called left outer join and
> vice versa.
>
> Thats what I noticed on most of the sites but my Oracle Press book says
> differently.
>
> When it appears on the right side, its called right outer join and vice
> versa.
>
> Pleaseeee guide me out of this confusion.
>
> Big Learner

Your book appears to be incorrect. Just because the book is from Oracle Press, does not mean that the book contents were authorized or reviewed by Oracle Corp for accuracy.

Oracle SQL Reference Manual (page 1298, 19-40): http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf (Quoting) "
SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_id, e.last_name;

Users familiar with the traditional Oracle Database outer joins syntax will recognize
the same query in this form:

SELECT d.department_id, e.last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+) ORDER BY d.department_id, e.last_name;
" (End Quoting)

http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join (Quoting) "
Example left outer join (ANSI 92 standard syntax): SELECT *
FROM employee

     LEFT OUTER JOIN
     department
       ON employee.DepartmentID = department.DepartmentID;

Example left outer join (non-standard syntax): SELECT *
FROM employee, department

     WHERE employee.DepartmentID = department.DepartmentID(+); " (End Quoting)

Note that Microsoft SQLServer uses different syntax. The above, written in SQLServer syntax appears as the following: SELECT *
FROM employee, department

     WHERE employee.DepartmentID *= department.DepartmentID;

As indicated by Michael Austin, as of Oracle 9i, Oracle supports ANSI 92 join syntax, which permits "LEFT OUTER JOIN" and "RIGHT OUTER JOIN" syntax in place of the Oracle specific (+) syntax. However, I disagree with Michael's comment that Oracle will depreciate the Oracle specific (+) syntax. Even in Oracle 10.2.0.2 there remain bugs in Oracle's implementation of the ANSI 92 join syntax - these bugs can easily be found in Metalink. Oracle apparently recommends the ANSI 92 join syntax over the Oracle specific syntax, as indicated on pages 503-505 of the Oracle SQL Reference Manual (link is above).

For me, it is much easier to decipher a complex SQL statement involving three or more tables if the joins are created in Oracle specific (+) syntax. All of the restrictions for what data is to be retrieved is included in the WHERE clause, and the multi-table join order is clearly defined also in the WHERE clause.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Dec 04 2006 - 09:01:59 CST