Outer Join Syntax

From: Daniel Z <dziesnospammer_at_zianet.com>
Date: Mon, 17 Jun 2002 15:48:49 -0600
Message-ID: <3d0e5b0a.0@oracle.zianet.com>

Perhaps someone can clear this up for me.

I was looking at Oracle's documentation: http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/ a90125/queries2.htm#2054625

Oracle says that "To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT OUTER JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B."

My interpretation of this is that if our WHERE statement is A=B, the use of the word LEFT is equivalent to putting the (+) on the RIGHT, and either one would display all of the rows from A, and any matching rows from B (or nulls).

Correct so far?

BUT... then Oracle gives me their examples: http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/ a90125/statements_103.htm#2066678

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;

Users familiar with the traditional Oracle 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;

WAIT A MINUTE! They're saying here that the use of LEFT is the same as putting the (+) on the left! I also looked this up in the 9i SQL Study Guide and they say the same thing. Groff's Complete SQL Reference says their opposites, like I understand it.

Could somebody confirm which is right? I only have 8i to work with at the moment, so I can't test the ANSI stuff. ANY assistance would be appreciated

Daniel Z.

Make sure there's "no spam" when replying Received on Mon Jun 17 2002 - 16:48:49 CDT