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

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