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

Yes you correctly identified a typo. The join condition should have been written as a.col1=b.col1.

My example, which admittedly could be improved, was the first one I came up with to try and show that when you outer join two tables together the ANSI sql syntax provides greater flexibility avoiding a number of documented Oracle issues with the (+) operator.

See
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/queries006.htm#SQLRF52354 for
a list of limitations.

Your point w/r to using the inline view is valid, although one might call that a trick as you are now not joining two tables but rather a table and an in-line view.

On Fri, Sep 25, 2009 at 5:28 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Without arguing with your conclusion, I have a few problems with your
> example and one of your points.
>
>
>
> First, I’m guessing you really meant the outer join to be on a.col1 =
> b.col1. Otherwise you’re filtering tabb to eliminate rows where col1 is null
> and there is no equijoin at all.
>
>
>
> Second, there is a really easy to distinguish the meanings in “traditional”
> (+) syntax. In fact writing that clearly establishes filters on the row
> sources in the from clause is arguably clearer. (But I am not making that
> argument here.)
>
>
>
> select *
>
> from
>
> taba a,
>
> (select * from tabb where col2 is null) b
>
> where a.col1 = b.col1(+);
>
>
>
> is crystal clear, at least to me. Likewise
>
>
>
> select *
>
> from
>
> taba a,
>
> tabb b
>
> where a.col1 = b.col1(+)
>
> and b.col2 is null
>
>
>
> is clear that only tuples where b.col2 is null are allowed in the result
> set, whether the tuple portion existed in tabb or was created in service of
> the outer join to taba.
>
>
>
> Finally, if you made the equivalent typo that I **presumed** you made in
> my **guess** (sorry Alex – but I’d need to do an extra ack-nak with Rumpi
> and wait for his reply to avoid a guess)
>
> then Oracle would helpfully respond:
>
>
>
> ORA-01416: two tables cannot be outer joined together
>
>
>
> (although in this case I suppose “a table cannot be outer joined to itself”
> might be a better message)a
>
>
>
> and that would force you to write something like
>
>
>
> select *
>
> from
>
> taba a,
>
> (
>
> select * from tabb
>
> where col1 is not null
>
> and col2 is null
>
> ) b
>
>
>
> if that is what you meant.
>
>
>
> I haven’t read your paper (yet), and I’ll be the first to excuse typos in
> quick helpful list responses (how many times have I typed the reverse of
> what I meant UNDO/REDO? [many
>
> and that’s a rhetorical question]). So please do not anyone think this is
> any sort of flame nor a quasi religious defense of (+) syntax, as I intend
> no argument. And I look forward to reading your paper.
>
>
>
> Regards,
>
>
>
> mwf
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Rumpi Gravenstein
> *Sent:* Wednesday, September 23, 2009 11:07 PM
> *To:* jkstill_at_gmail.com
> *Cc:* sacrophyte_at_gmail.com; ORACLE-L
> *Subject:* Re: SOLVED?: Oracle interprets ANSI Inner Join as an Outer
> Join?
>
>
>
> <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
>
>

-- 
Rumpi Gravenstein

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