From: | "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com> |
---|---|
To: | "Patrick Kay" <patk(at)auctionsolutions(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Outer join in postgresql |
Date: | 2000-06-12 21:53:48 |
Message-ID: | 00061217591900.29937@comptechnews |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Mon, 12 Jun 2000, Patrick Kay wrote:
> I am looking for a way run an outer join in psql. Can anyone help?
>
> Informix has an "OUTER" keyword. I don't see anything like this in the docs
> for psql.
>
> Thanks much.
> -Pat Kay
Here is an example I have about simulating an outer join in PostgreSQL:
(hope this is correct, I'm still a bit of a novice but getting better)
CREATE TABLE master (m_id INTEGER, m_name TEXT);
CREATE TABLE slave (m_id INTEGER, s_name TEXT);
INSERT INTO master (m_id, m_name) VALUES (1, 'Satan');
INSERT INTO master (m_id, m_name) VALUES (2, 'God');
INSERT INTO slave (m_id, s_name) VALUES (1, 'Joe');
INSERT INTO slave (m_id, s_name) VALUES (1, 'Tom');
SELECT * FROM master m, slave s
WHERE m.m_id = s.m_id
UNION
SELECT *, NULL, 'no slaves'
FROM master m
WHERE m.m_id NOT IN ( SELECT m_id FROM slave );
m_id | m_name | m_id | s_name
------+--------+------+-----------
1 | Satan | 1 | Joe
1 | Satan | 1 | Tom
2 | God | | no slaves
(3 rows)
-- Oracle outer-join
SELECT * FROM master m, slave s
WHERE m.m_id = s.m_id (+);
m_id | m_name | m_id | s_name
------+--------+------+-----------
1 | Satan | 1 | Joe
1 | Satan | 1 | Tom
2 | God | |
(3 rows)
I keep a file at http://comptechnews.com/~reaster/dbdesign.html that has
some info that might be useful to some people.
--
Robert B. Easter
From | Date | Subject | |
---|---|---|---|
Next Message | Bernie Huang | 2000-06-12 22:32:25 | Problem regarding 'select...as...' |
Previous Message | Patrick Kay | 2000-06-12 21:01:52 | Outer join in postgresql |