Re: JOIN vs. LEFT JOIN

Thanks a lot Tom for explaining !


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Obe, Regina" <robe.dnd@cityofboston.gov>
Cc: "Nico Callewaert" <callewaert.nico@telenet.be>; "Andreas Wenk"
<a.wenk@netzmeister-st-pauli.de>; <pgsql-novice@postgresql.org>
Sent: Wednesday, January 28, 2009 5:49 PM
Subject: Re: [NOVICE] 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
>
>