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)
);

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;

If someone does a select from project_view and does not select the
partner column, the join can be eliminated, because the not null and
foreign key constraints on the partner_id column guarantee that there
will always be exactly one matching row in the project table.

If you didn't have the NOT NULL constraint on the partner_id column,
you'd have to write the view this way, as described in the original
email:

CREATE VIEW project_view AS
SELECT p.id, p.name, p.partner_id, pp.name AS partner
FROM project p LEFT JOIN partner pp ON p.partner_id = pp.id;

In this example, I just have one join, so the benefit to eliminating it
is minimal (unless the tables are very large). But in the real
application, project_view joins the project table against six other
tables using inner joins (all against the primary keys of those other
tables) and four additional tables using left joins (also against the
primary keys of those other tables). Most queries only use a subset of
these columns - a typical query requires evaluating only about three of
the ten joins.

...Robert