Re: DELETE syntax on JOINS

Greg Stark <gsstark@mit.edu> writes:
> You would have to write something like

> UPDATE foo set a = (select aa from bar where...)
>                          b = (select bb from bar where...)

> and then the optimizer would have to notice the duplicates and
> consolidate them? That seems inconvenient (and fragile).

Well, that's why the spec nowadays allows you to write
UPDATE foo SET (a,b) = (select aa,bb from bar where ...)

But we haven't got that, and if we did it would generate a nestloop
plan.  Getting to the point of absolute performance equivalence between
subqueries and joins would take a *lot* of work; I'm not even sure it's
possible at all.  And once we'd done all that work there would still
remain the fact that people are accustomed to using join syntax instead.
There's a lot of existing code out there that would be a lot easier
to port to PG if we supported that style (which was exactly the point
made by the OP).
        regards, tom lane