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 partner pp ON p.partner_id = pp.id;
3. wait (or implement :-) the left join optimization in pg

Andreas