From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Dan Janowski <danj@3skel.com>
To: J_Shevland@TurnAround.com.au, M Simms <grim@argh.demon.co.uk>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] OUTER JOINS
Date: Wed, 28 Apr 1999 18:58:14 +0300
Message-ID: <l03130305b34cd633efee@[147.233.159.109]> (raw)
In-Reply-To: <371B2F13.D8B446F6@3skel.com>
References: <199904152011.QAA26538@fnur.3skel.com>
<371ACCB0.6A437C5@TurnAround.com.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, danj@3skel.com, grim@argh.demon.co.uk
Subject: Re: [SQL] OUTER JOINS
In-Reply-To: <l03130305b34cd633efee@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox