Join questions

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-general(at)postgresql(dot)org
Subject: Join questions
Date: 2001-08-22 15:08:39
Message-ID: 20010822100839.A13537@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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]+)*$')
);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lamar Owen 2001-08-22 15:10:47 Re: New RPMS ?
Previous Message newsreader 2001-08-22 15:03:01 Re: FTI is really really slow; what am I doing wrong?