Thread: JOINS...

Whats the purpose of joins ?? is it the same as selecting from mulitple tables ?
--
____________________________________________
http://www.operamail.com
Get OperaMail Premium today - USD 29.99/year


Powered by Outblaze

Hi novice,

>Whats the purpose of joins ?? is it the same as selecting from mulitple tables ?
>
>
In a Relational Database liek Postgresql, you try the store data which
as few redundancy as possible.
The result is that you store the data in many tables - eg. persons and
adresses will be separate
(this process is called Normalisation).
Now, to put the corresponding data together again, you need a join
between the tables that belong together.
In order to work, you need at least (numberOfTables -1) Join-Conditions,
for exaple

SELECT
person.name,
address.line1
FROM
person,
address
WHERE
' Here comes the Join Condition
address.person_id = person.person_id

If you do not specify the Join Condition, you will get all
possible combinations of  persons and addresses,
the so called cartesian Product of the 2 tables.

Cheers, Dani

Hi Dani,

I understand that part :)

but what about the extra options you can specify in the join condition, like 'outer' , 'inner', 'full' etc

i dont see a circumstance when you would use the extra options...

----- Original Message -----
From: Dani Oderbolz <oderbolz@ecologic.de>
Date: Fri, 09 May 2003 10:29:51 +0200
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] JOINS...

> Hi novice,
>
> >Whats the purpose of joins ?? is it the same as selecting from mulitple tables ?
> >
> >
> In a Relational Database liek Postgresql, you try the store data which
> as few redundancy as possible.
> The result is that you store the data in many tables - eg. persons and
> adresses will be separate
> (this process is called Normalisation).
> Now, to put the corresponding data together again, you need a join
> between the tables that belong together.
> In order to work, you need at least (numberOfTables -1) Join-Conditions,
> for exaple
>
> SELECT
> person.name,
> address.line1
> FROM
> person,
> address
> WHERE
> ' Here comes the Join Condition
> address.person_id = person.person_id
>
> If you do not specify the Join Condition, you will get all
> possible combinations of  persons and addresses,
> the so called cartesian Product of the 2 tables.
>
> Cheers, Dani
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--
____________________________________________
http://www.operamail.com
Get OperaMail Premium today - USD 29.99/year


Powered by Outblaze

>
>
>Hi Dani,
>
>I understand that part :)
>
>but what about the extra options you can specify in the join
>condition, like 'outer' , 'inner', 'full' etc
>
>i dont see a circumstance when you would use the extra options...
>
>
Hi,
these options are useful when your tables are partially related.
For example, you have a table products and a table color.
Now some products don't have a color (like a computer Program).
If you would state this join:

Select product.name, color.name
from
product,
color
where product.color_id = color.color_id (This is an INNER Join, by the Way)

You would NOT retreive the products whose
color_id is NULL.

Thats where the OUTER JOIN comes in - but this is - as far as i know-
not directly supported in Postgres.
You have to do this:

Select product.name, color.name
from
product,
color
where product.color_id = color.color_id
UNION
Select product.name, 'No color'
from
product
where
color_id IS NULL;

You find a nice introduction on all this here:

http://spot.colorado.edu/~marangak/main.html

(But they speak about Oracle, not Postgresql)

I hope this gives you some clues.

Cheers, Dani

On Tue, 2003-05-13 at 08:01, Dani Oderbolz wrote:
> Thats where the OUTER JOIN comes in - but this is - as far as i know-
> not directly supported in Postgres.
> You have to do this:
>
> Select product.name, color.name
> from
> product,
> color
> where product.color_id = color.color_id
> UNION
> Select product.name, 'No color'
> from
> product
> where
> color_id IS NULL;

This advice is out of date.  PostgreSQL does indeed support outer joins
(since 7.1, I think).

SELECT product.name, COALESCE(color.name,'No color') FROM product LEFT
OUTER JOIN color ON product.color_id = color.color_id;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Watch ye and pray, lest ye enter into temptation. The
      spirit truly is ready, but the flesh is weak."
                           Mark 14:38