Re: prefer (+) oracle notation

"Edmar Wiggers" <edmar(at)brasmap(dot)com> writes:
> I'm not sure about the standard, but I really like Oracle's notation for
> foreign keys:

> select a.item_number, b.group_code_description
> from items a, group_codes b
> where a.group_code = b.group_code (+);

I beg to differ --- IMHO, Oracle's notation is brain dead. If they had
stuck the (+) markers on FROM-list items, it would have been sort of
reasonable, but as is, it's incomplete and ambiguous. Incomplete
because you can't specify an outer join against a table that's not
referenced anywhere in the WHERE clause. Ambiguous because it's not
clear what it means if you reference several columns from the same table
in WHERE, and tag some of them with (+) and others not. Does that mean
you get an outer join anyway? Is it an error condition? Maybe you
should implicitly get two FROM-list items, one outer joined and one not?

Worse, if you have more than two FROM-items it's very unclear what the
Oracle syntax means at all. There is a big difference between
(A CROSS JOIN B) LEFT JOIN C
and
A CROSS JOIN (B LEFT JOIN C)
not to mention
(A LEFT JOIN C) CROSS JOIN B
but who is to say which of these behaviors you will get from, say,
select ... from A, B, C where a1 = b1 and a2 = c2 (+)
? And if you reorder the terms in the WHERE, do you get a different
answer? It gets a lot worse if more than one table is outer-joined.

I don't have any great love for the ISO syntax either; it's certainly
mighty verbose. But at least you can tell what the heck it means.

regards, tom lane