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

Re: JOIN vs. LEFT JOIN

From: Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
To: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
Cc: Nico Callewaert <callewaert(dot)nico(at)telenet(dot)be>, pgsql-novice(at)postgresql(dot)org
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 12:44:31
Message-ID: 4980532F.3030102@netzmeister-st-pauli.de (view raw or flat)
Thread:
Lists: pgsql-novice
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Obe, Regina schrieb:
>> -----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.

Cool - this is a good "preaching" to be totally correct and academic ;-)

Thank you !

Cheers

Andy

- --
St.Pauli - Hamburg - Germany

Andreas Wenk

> 
> 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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJgFMvVa7znmSP9AwRAgqIAKCk8z1rJLXaS8GnK7FsWdOjl+LjawCgnGvm
WXob+X3CZt0VRSgu8fM9ZaY=
=3UTC
-----END PGP SIGNATURE-----

In response to

pgsql-novice by date

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

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