Re: JOIN vs. LEFT JOIN

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