Received: from comptechnews.com (root@cc993546-b.srst1.fl.home.com [24.3.77.52]) by hub.org (8.10.1/8.10.1) with ESMTP id e5CLvtT52928 for ; Mon, 12 Jun 2000 17:57:55 -0400 (EDT) Received: from comptechnews (reaster@localhost [127.0.0.1]) by comptechnews.com (8.9.3/8.9.3) with SMTP id RAA30080; Mon, 12 Jun 2000 17:59:19 -0400 From: "Robert B. Easter" To: "Patrick Kay" Subject: Re: Outer join in postgresql Date: Mon, 12 Jun 2000 17:53:48 -0400 X-Mailer: KMail [version 1.0.28] Content-Type: text/plain References: In-Reply-To: Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Message-Id: <00061217591900.29937@comptechnews> Content-Transfer-Encoding: 8bit X-Archive-Number: 200006/125 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