From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Dan Janowski <danj(at)3skel(dot)com>, J_Shevland(at)TurnAround(dot)com(dot)au, M Simms <grim(at)argh(dot)demon(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] OUTER JOINS |
Date: | 1999-04-28 15:58:14 |
Message-ID: | l03130305b34cd633efee@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
At 16:26 +0300 on 19/04/1999, Dan Janowski wrote:
> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.
To the best of my knowledge, outer joins are not yet supported in
PostgreSQL. As far as I recall, you can achieve the same effect with a
union.
For example, if you have a people table and a pets table, where each
person's pet is joined to the owner by the owner's id colum, you'll
theoretically do something like:
SELECT surname, firstname, pet_name
FROM people, pets
WHERE id *= owner_id
ORDER BY surname, firstname;
So instead, you'll have to do something like:
SELECT surname, firstname, pet_name
FROM people, pets
WHERE id = owner_id
UNION
SELECT surname, firstname, null
FROM people
WHERE not exist (
SELECT 1
FROM pets
WHERE owner_id = id
)
ORDER BY surname, firstname;
Excuse me if I goofed something syntactically, I don't have time to
actually create the tables and test the above. Note, however, that a UNION
removes duplicates.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Vernichon | 1999-04-28 20:56:01 | |
Previous Message | Nuchanard Chiannilkulchai | 1999-04-28 12:02:32 | Re: [SQL] substring |