Re: Outer join by (+)
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Wed, 02 Jan 2008 08:47:50 -0800
Robert Klemme wrote:
On 02.01.2008 15:33, Brian Tkatch wrote:On Sat, 29 Dec 2007 10:56:45 +0000, Adam Cameron
<adam_junk@xxxxxxxxxxx> wrote:
But when I use (+) syntax, it will be easy to design and understand.Not if the person reviewing your code is more accustomed to ANSI-standard SQL than out-of-date Oracle peculiarities.
I can read the standard SQL just fine, because that's what I'm used to. I have to stop and think about the Oracle-specific stuff because I'm less familiar with it.
I presume it's the other way around for you for the reverse reasons.
I find the (+) is very intuitive. In describes the join in the WHERE
clause quite well. Basically, it means that that this join is a join
"plus". It just takes a moment to realize that is what it means.
The ANSI syntax, however, is downright confusing.
You should add a "for me" at the end of the sentence above. This is exactly what Adam's comment was all about: some prefer one and others prefer the other.
Further, i think it breaks the SQL paradigm. A FROM clause includes, a
WHERE clause excludes. Another TABLE in the FROM clause includes it,
saying it is not Cartesian, is an exclusion. Therefore, it belongs in
the WHERE clause.
What exactly is the "SQL paradigm"? I know only a SQL standard (or rather several versions of it) which defines certain semantics for particular syntactical constructs. As far as I can see JOIN's are properly defined within the standard and do not break anything (because it was a new construct when added).
Both situations hereare entirely reasonable, I think, but *looking forward*, sticking to standards-conforming SQL is probably the better approach.
IMO.
Good point. I would like to counter point.
A person looking forward to a maintenance coder who probably will
spend less time than required modifying a query, may have a harder
time figuring out where to put a new clause. That is, whether it goes
in the FROM clause or the WHERE clause, and how it affects to total
join, as nesting is a big issue. However, when all clauses are kept in
the WHERE clause, the maintenance coder knows exactly where it goes,
and, IMO, is more likely to do it correctly.
This still leaves the issue of lesser expressiveness for the proprietary Oracle syntax (see the link I posted earlier).
Kind regards
robert
The endless, and meaningless, debate about which is more intuitive or
which has the prettiest color, or whatever is amusing. Lets look at
reality.
SELECT *
FROM t1, t2
WHERE t1.col = t2.col;
replace the comma with the word JOIN
replace the word WHERE with the word ON
and you get
SELECT *
FROM t1 JOIN t2
ON t1.col = t2.col;
Lets see one byte becomes four so add 3
and five bytes become two so subtract 3
And for this people get excited?
No doubt with a RIGHT OUTER JOIN you can stave off carpal tunnel
syndrome for an additional 15 minutes by using (+) but is this
really worth all the keystrokes that go into this seemingly
endless discussion?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- Follow-Ups:
- Re: Outer join by (+)
- From: Robert Klemme
- Re: Outer join by (+)
- References:
- Re: Outer join by (+)
- From: Robert Klemme
- Re: Outer join by (+)
- Prev by Date: Re: Oracle 10g Training Slides
- Next by Date: Re: Cartesian outer join with plus-sign syntax
- Previous by thread: Re: Outer join by (+)
- Next by thread: Re: Outer join by (+)
- Index(es):
Relevant Pages
|
