Re: prefer (+) oracle notation - Mailing list pgsql-general

"Edmar Wiggers" <edmar@brasmap.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