Re: Outer join by (+)

On Wed, 02 Jan 2008 17:28:05 +0100, Robert Klemme <shortcutter_at_googlemail.com> wrote:

>On 02.01.2008 15:33, Brian Tkatch wrote:
>> On Sat, 29 Dec 2007 10:56:45 +0000, Adam Cameron
>> <adam_junk_at_hotmail.com> 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.

I guess. Sorry about that. I meant the entire reply as an opinion, as this is a highly opinionated topic.

>> 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).

Personally, i think the paradigm, sans the ANSI the JOIN syntax, is obvious. It is the way i understand SQL, and the way i teach it to others (when asked).

>>> 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).

You mean the lack of a full outer join? Yep, defintiely a deficiency. But, IIRC, that can be fixed via a VIEW.

I agree, to each their own. I just want to make sure people don't think that everyone agrees that the ANSI syntax is better. I regard it more as a plague and a hinderance to truly understanding (the rest of) SQL. B.

>
>Kind regards
>
> robert
Received on Wed Jan 02 2008 - 11:23:00 CST