RE: SOLVED?: Oracle interprets ANSI Inner Join as an Outer Join?
Date: Fri, 25 Sep 2009 05:28:20 -0400
Message-ID: <223960F38A0A43BB8939EAFEEBC402F1_at_rsiz.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 25 2009 - 04:28:20 CDT