Re: JOIN vs. LEFT JOIN

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
Cc: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>, "Andreas Wenk" <a(dot)wenk(at)netzmeister-st-pauli(dot)de>, pgsql-novice(at)postgresql(dot)org
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 16:49:29
Message-ID: 18135.1233161369@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-01-28 17:10:57 Re: UUId or Similar
Previous Message Morris, Roy 2009-01-28 15:05:31 Re: UUId or Similar