Re: JOIN vs. LEFT JOIN

From: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 20:50:11
Message-ID: 2655E082C2C2460683342FA387FCEABB@etsinformatics.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Thanks a lot Tom for explaining !

----- Original Message -----
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>
Sent: Wednesday, January 28, 2009 5:49 PM
Subject: Re: [NOVICE] JOIN vs. LEFT JOIN

> "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

Browse pgsql-novice by date

  From Date Subject
Next Message Rebecca Jones 2009-01-29 15:51:01 REALLY stupid question
Previous Message Tom Lane 2009-01-28 17:10:57 Re: UUId or Similar