Re: SOLVED?: Oracle interprets ANSI Inner Join as an Outer Join?

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Wed, 23 Sep 2009 23:07:27 -0400
Message-ID: <9c9b9dc90909232007i46a6f84ch1d8b4e1db4d6999a_at_mail.gmail.com>

<snip>It is simpler to write, and easier to read IMO.</snip>

I agree!

This is a topic close to my heart. In my view one should only use the ANSI syntax. The Oracle SQL documentation recommends it for outer joins. I recommend it for all joins.

Here are some of the reasons:

ANSI outer joins have more power as they distinguish between these two statements which can't easily be distinguished with the traditional syntax:

SELECT *
  FROM taba A

            LEFT OUTER JOIN tabb B
            ON ( b.col1 = b.col1
                   AND b.col2 IS NULL


                  )

and

SELECT *
  FROM taba A

            LEFT OUTER JOIN tabb B
            ON ( b.col1 = b.col1


                  )

  WHERE b.col2 IS NULL

The first applies the b.col2 IS NULL filter before the outer join is attempted while the second applies the filter after the outer join is completed. That can lead to completely different results!

Also, writing "correct" ANSI FULL join syntax is easy while doing the same with the traditional (+) syntax is not.

I love the CROSS JOIN statement as it explicitly states what you want instead of leaving it to comments/reader who may later think the SQL statement is in error.

As for the INNER syntax, the close juxtaposition of the table and the join condition makes the statement eminently more readable and therefore easier to maintain.

One caveat. Stay away from the NATURAL join as it can lead to trouble. My recommendation is to always use the ON syntax to specify table join conditions.

I could go on and on on this topic but wont as I've done it in a presentation last year...

Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 23 2009 - 22:07:27 CDT