OUTER JOINs in PostgreSQL

From: Ant9000 <ant9000(at)seldati(dot)it>
To: pgsql-sql(at)postgreSQL(dot)org
Subject: OUTER JOINs in PostgreSQL
Date: 1999-06-02 20:47:09
Message-ID: 99060222482601.00588@chomp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi, I was trying to do an apparently simple task: I have two tables, say

master
id | name
---------
1 | Alpha
2 | Beta
3 | Gamma

detail
-------
id | lastvisit | info
---------------------
1 | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Wed Jun 02 19:45:08 1999 CEST | some more blah's

Now, I'd like to list all of the fields of table 'master', together with the
fields lastvisit and info from 'detail' if they have a corresponding value:
ie, I'd like some SQL that gives me

id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
2 | Beta | |
3 | Gamma | |

The best I was able to obtain is this:

SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id
UNION
SELECT *,NULL AS lastvisit,NULL AS info FROM master
WHERE id NOT IN (SELECT id FROM detail);

which is (at best) unelegant; in MS Access you could do something like

SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
ON master.id=detail.id;

With Oracle, there's an even shorter solution:

SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id(+);

Is anything like that available with PostgreSQL?

Thanks in advance,
Ant9000

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jason Earl 1999-06-02 21:08:33 Re: [SQL] OUTER JOINs in PostgreSQL
Previous Message Tiffany Renee' Davis 1999-06-02 19:20:15 Did version 6.5 make foreign keys functional?