Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Tom LaneDate: 2009-01-28 17:10:57
Subject: Re: UUId or Similar
Previous:From: Morris, RoyDate: 2009-01-28 15:05:31
Subject: Re: UUId or Similar

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group