Postgres Pro
Downloads
Home   >   mailing lists

Thread: Outer join in postgresql

Outer join in postgresql

From
"Patrick Kay"
Date:
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

Re: Outer join in postgresql

From
Peter Vazsonyi
Date:
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
> 

Hmmm... I don't now the exact definition of outer join.
I feel it may somethink like this:

SELECT * FROM a WHERE NOT EXISTS (SELECT id FROM b WHERE b.id=a.id);

This works in postgresql, but doesn't use indices ;(
I mean this runs an sequencial scan on a, and many index scan on b.
The result may be done with a special hash-join, but not in current
versions.
I have a strange sens, in 7.1 it will be... regards
--nek;(

Re: Outer join in postgresql

From
Ed Loehr
Date:
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.

There are many examples on how to do this in the archives or via
deja.com's power search.

Regards,
Ed Loehr

Re: Outer join in postgresql

From
Bruce Momjian
Date:
See the FAQ.  It involves UNION.

> 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.
> 
> There are many examples on how to do this in the archives or via
> deja.com's power search.
> 
> Regards,
> Ed Loehr
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: Outer join in postgresql

From
"Robert B. Easter"
Date:
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

Re: Outer join in postgresql

From
Jesus Aneiros
Date:
There is no OUTER JOIN in postgres. You could use SELECT and UNION ALL.

Jesus.

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
>