OUTER JOINS in PostgreSQL - Mailing list pgsql-sql

From ant9000@c0a111.science.unitn.it
Subject OUTER JOINS in PostgreSQL
Date
Msg-id Pine.LNX.4.04.9906022013370.14777-100000@c0a111.science.unitn.it
Whole thread Raw
List 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



pgsql-sql by date:

Previous
From: "Pham, Thinh"
Date:
Subject: RE: [SQL] Column name's length
Next
From: David Sauer
Date:
Subject: how to delete access rights from non-existent user ?