Re: JOIN vs. LEFT JOIN

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