Thread: Join questions

I have two related questions about joins.

One is that if you don't group with parenthesis, what order are they
done in?

Will the optimizer be able to pick the better order of the two possible
orders in the following example or do I need to try both and pick one?

select accerate, colodesc, editcode from
  (acce natural join colo) natural left join edit;

select accerate, colodesc, editcode from
  (acce natural left join edit) natural join colo;

The above are equivalent because there is exactly one colo record that will
match up with each acce record. (The table definitions are below.)

create table colo (
  colocode serial primary key,
  colodesc text unique not null
    constraint bad_colodesc check (colodesc ~ '^[\041-\176]+( [\041-\176]+)*$'),
  colonote text
    constraint bad_colonote check (colonote ~ '^[\041-\176]+( [\041-\176]+)*$')
);

create table acce (
  accecode serial primary key,
  accerate numeric(2,1) unique not null,
  colocode int4 not null
    constraint bad_colocode references colo,
  accenote text
    constraint bad_accenote check (accenote ~ '^[\041-\176]+( [\041-\176]+)*$')
);

create table edit (
  editcode serial primary key,
  titlcode int4 not null
    constraint bad_titlcode references titl,
  langcode int4 not null
    constraint bad_langcode references lang,
  accecode int4
    constraint bad_accecode references acce,
  editnote text
    constraint bad_editnote check (editnote ~ '^[\041-\176]+( [\041-\176]+)*$')
);

On Wed, Aug 22, 2001 at 11:54:43AM -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > I have two related questions about joins.
>
> The latter.  See
> http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html

Thanks, this is what I was looking for.

When I looked before I started at the documentation for the select command
and didn't think to look in the performance tips area for additional
information (though I had been through that area before I didn't remember
that it had information about joins that might answer the specific question
I thought up this morning).

I think it would be useful to have a link from the section on joins under
the select command documentation to the information on joins under
performance tips, since that extra information gives additional information
on the semantics of join commands in addition to actual perfomance tips.

Bruno Wolff III <bruno@wolff.to> writes:
> I have two related questions about joins.
> One is that if you don't group with parenthesis, what order are they
> done in?

Left to right.  A JOIN B JOIN C == (A JOIN B) JOIN C.

> Will the optimizer be able to pick the better order of the two possible
> orders in the following example or do I need to try both and pick one?

The latter.  See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html

This behavior is not graven on stone tablets (at least not for inner
joins), but it was easy to do and is useful for cases where you *don't*
want the planner to try all possible join orders.  So it'll probably
stay like that at least for a release or two, until we have enough field
experience to see whether people like it this way or not.

            regards, tom lane