Re: Eliminating unnecessary left joins

> I have this exact problem a lot.  There are actually cases
> where you can eliminate regular joins, not just left joins.
> For example:
>
> CREATE TABLE partner (
>     id            serial,
>     name            varchar(40) not null,
>     primary key (id)
> );
>
> CREATE TABLE project (
>     id            serial,
>     name            varchar(40) not null,
>     partner_id        integer not null references project (id)
^^^^^^^ -- I assume typo, should be partner
> );
>
> CREATE VIEW project_view AS
> SELECT p.id, p.name, p.partner_id, pp.name AS partner FROM
> project p, partner pp WHERE p.partner_id = pp.id;

Same advice to you:

1. add not null to your id's
2. CREATE VIEW project_view AS  SELECT p.id, p.name, p.partner_id, pp.name AS partner FROM   project p left outer join
partnerpp ON p.partner_id = pp.id; 
3. wait (or implement :-) the left join optimization in pg

Andreas