pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robert B. Easter <reaster@comptechnews.com>
To: Patrick Kay <patk@auctionsolutions.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Outer join in postgresql
Date: Mon, 12 Jun 2000 17:53:48 -0400
Message-ID: <00061217591900.29937@comptechnews> (raw)
In-Reply-To: <NEBBIHGDAKOMPPPBIFNPKEEBCAAA.patk@auctionsolutions.com>
References: <NEBBIHGDAKOMPPPBIFNPKEEBCAAA.patk@auctionsolutions.com>

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



view thread (13+ messages)  latest in thread

Message-ID: <00061217591900.29937@comptechnews>
Permalink:  ../00061217591900.29937@comptechnews/
Also on:    postgresql.org/message-id/00061217591900.29937@comptechnews

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: reaster@comptechnews.com, patk@auctionsolutions.com
  Subject: Re: Outer join in postgresql
  In-Reply-To: <00061217591900.29937@comptechnews>
  References: <NEBBIHGDAKOMPPPBIFNPKEEBCAAA.patk@auctionsolutions.com> <00061217591900.29937@comptechnews>

* 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