Re: JOIN vs. LEFT JOIN - Mailing list 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):
>
> books:
>
> headline |   content
> - ----------+-------------
> politics | russia
> politics | middle east
> politics | germany
>
> interests:
>
> content
> - ---------
> germany
>
> Then fire two select statements like this:
>
> #1:
> SELECT a.headline,a.content,b.content as contentb
> FROM books a
> JOIN interests b ON a.content = b.content;
>
>  headline | content | contentb
> - ----------+---------+----------
> politics | germany | germany
> (1 row)
>
> #2:
> SELECT a.headline,a.content,b.content as contentb
> FROM books a
> LEFT JOIN interests b on a.content = b.content;
> headline |   content   | contentb
> - ----------+-------------+----------
> politics | russia      |
> politics | middle east |
> politics | germany     | germany
> (3 rows)
>
>> But it's barely possible to use full JOINS all the time, since most of
>> the lookup fields are not required.
>> Example : in the customer table, the customer type is a looup field to a
>> customer_type table.  But that is not required to post the record.  So I
>> was thinking to create a record in the customer_type table with ID =
>> -1.  And in case the customer type is NULL, to assign it the value -1.
>> That way, it will be always possible to do a full JOIN.  I was wondering
>> if that is good practice or rather nonsense ?
>
> Hm concerning the functionality of LEFT JOIN I do not really understand
> what you wanna do
> here. You created the customer_type table to have the possibility to give
> more types to
> one customer (1:n). But therefore you do not need a LEFT JOIN. The
> statement could be like:
>
> SELECT name a
> FROM customer a, cutomer_type b
> WHERE a.id = b.id
> AND b.type = 'super customer'
>
> Or not?
>
> Cheers
>
> Andy


Hi Andreas,

Thanks for the reply.  I know the difference between JOIN and LEFT JOIN.
The question was just if there is a performance hit between the 2 ?

I never use constructs like this : SELECT name a
> FROM customer a, cutomer_type b
> WHERE a.id = b.id
> AND b.type = 'super customer'

Always Joins.
I will try to clarify.  The current situation is : the customer_type has no
value, so = NULL, no problem with LEFT JOIN.
But I'm wondering with tables that has thousands of records, if LEFT JOINS
are performing well ?

Thanks again, Nico