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

Re: JOIN vs. LEFT JOIN

From: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>
To: "Andreas Wenk" <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 11:30:24
Message-ID: C31D2FB57EE74ECF9EEA558E0046DDCA@etsinformatics.local (view raw or flat)
Thread:
Lists: 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 



In response to

Responses

pgsql-novice by date

Next:From: Andreas WenkDate: 2009-01-28 11:46:57
Subject: Re: JOIN vs. LEFT JOIN
Previous:From: Andreas WenkDate: 2009-01-28 11:22:37
Subject: Re: JOIN vs. LEFT JOIN

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