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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message Nico Callewaert 2009-01-28 12:49:35 Re: JOIN vs. LEFT JOIN
Previous Message Andreas Wenk 2009-01-28 12:41:47 Re: JOIN vs. LEFT JOIN