Re: prefer (+) oracle notation

"Edmar Wiggers" <edmar(at)brasmap(dot)com> writes:
> select a.id,a.size,b.*,c.id,c.color
> from table_a a, table_b b, table_c c
> where
> a.b_id = b.id and
> a.c_id = c.id(+) and
> a.size < 1000 and
> b.weight > 10;

> This is a select from 3 tables, where a and b are regularly joined, but c is
> outer joined. That is, the query is likely to return null values on c.id and
> c.color.

Yes, but outer joined *to what*? And what aspect of the syntax decides
that? The problem with this syntax is that it's not apparent when the
WHERE-clause conditions are applied. At least not to me.

The problem can be seen most easily when there are additional
restrictions on table C. Actually we don't need 3 tables, so consider

select * from table_a a, table_b b
where
a.id = b.id(+) and
b.weight > 10;

with data

a.id

1
2

b.id b.weight

1 20
2 5

Clearly we will get a row 1,1,20, and we will *not* get a row 2,2,5.
But will we get a row 2,NULL,NULL, or not? If the outer join occurs
after we remove b rows with weight<=10, then there will be no row
matching a.id=2, so the outer join will produce a row 2,NULL,NULL.
If it's done in the other order, the outer join will produce a row
2,2,5, which will then be discarded due to the condition weight>10,
so no row out. The ISO syntax allows both these behaviors to be
expressed unambiguously:

... FROM a LEFT JOIN b ON (a.id = b.id AND b.weight > 10)
... FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.weight > 10

whereas the Oracle syntax is very ambiguous --- please don't tell me
that it depends on the ordering of the AND clauses in the WHERE!

(You may argue that 2,NULL,NULL violates the condition b.weight > 10,
but that's just an artifact of trying to simplify the example as much
as possible. If I write
where
a.id = b.id(+) and
(b.weight > 10 OR b IS NULL);
then it's absolutely unclear which result the Oracle syntax should
produce.)

It gets a lot worse if there are multiple tables being outer-joined,
since then it will depend on the join order whether you get certain
part-NULL rows out or not, and I see no way to define the join order
in the Oracle syntax.

> I believe the standard syntax for that might be:

> select a.id,a.size,b.*,c.id,c.color
> from ((table_a a join table_b b on a.b_id = b.id) outer join table_c c on
> a.c_id = b.id)
> where
> a.size < 1000 and
> b.weight > 10;

> To me, not so readable. But of course I can live with that.

Like I said, I don't much care for the ISO syntax either --- it's
very verbose. But it's unambiguous what will happen...

regards, tom lane