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

Re: JOIN vs. LEFT JOIN

From: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
To: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>,"Andreas Wenk" <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 12:27:48
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D20554818A@ZDND.DND.boston.cob (view raw or flat)
Thread:
Lists: pgsql-novice
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Nico Callewaert schrieb:
>> Hi !
>>
>> I heard that it is always better to use a full JOIN rather than a
LEFT
>> JOIN, for performance reasons.  Is that correct ?
>
> please note that a JOIN and a LEFT JOIN are tow different things.
Let's 
> assume you have
> two tables (without any sens):
>

This is a bit of the nitpick, but please don't call an INNER JOIN a FULL
JOIN.

Those are 2 separate animals altogether.  

JOIN and INNER JOIN are the same, but I tend to put in the word INNER
for clarity 
even though some may consider it redundant.

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
			and the performance hit is not worth the effort
of changing your data model to put in junk data that 
			should be rightfully NULL, plus there are other
nifty tricks you can perform with LEFTS as I have here)
	
http://www.bostongis.com/blog/index.php?/archives/37-Explain-Analyze-Geo
metry-Relation-Operators-and-Joins-Except-Where.html

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

Okay enough of my preaching.

Go forth and prosper.

Thanks,
Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

In response to

Responses

pgsql-novice by date

Next:From: Andreas WenkDate: 2009-01-28 12:41:47
Subject: Re: JOIN vs. LEFT JOIN
Previous:From: Nico CallewaertDate: 2009-01-28 12:21:33
Subject: Re: JOIN vs. LEFT JOIN

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