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