Re: JOIN vs. LEFT JOIN

"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> So in terms of performance

> [INNER] JOIN -- fastest
> LEFT JOIN -- generally slower (but there really is no alternative if you
> don't want to leave out records  without matches

This is nonsense.  A left join is not inherently slower than an inner
join.

What *is* true is that a left join constrains the optimizer more than an
inner join, ie some join reorderings are allowed for inner joins but
would change the answers if an outer join is involved.  So in the
context of a specific query you might get a slower plan if you use a
left join.  But you can't say that as a blanket statement.  In a lot
of cases there won't be any difference at all (particularly with more
recent PG versions --- our optimizer has gotten smarter about outer
joins over time).

> FULL JOIN -- rarely used and slower than INNER, LEFT, RIGHT, but it
> comes in handy at times.

Likewise, a full join isn't necessarily slow in itself, but it
constrains the possible plans quite a lot.

            regards, tom lane