Re: JOIN vs. LEFT JOIN

From: Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
To: Nico Callewaert <callewaert(dot)nico(at)telenet(dot)be>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: JOIN vs. LEFT JOIN
Date: 2009-01-28 11:22:37
Message-ID: 49803FFD.1090900@netzmeister-st-pauli.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
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
- --
St.Pauli - Hamburg - Germany

Andreas Wenk
> Many thanks in advance,
> Nico
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJgD/9Va7znmSP9AwRAsyDAKC8utO2Agy0ONULuy7nIgz9pG/7rgCfa/li
o98EaJSKGqkv2brcd0RcI04=
=de2X
-----END PGP SIGNATURE-----

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Nico Callewaert 2009-01-28 11:30:24 Re: JOIN vs. LEFT JOIN
Previous Message Nico Callewaert 2009-01-28 10:43:24 JOIN vs. LEFT JOIN