Thread: OUTER JOINs in PostgreSQL
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
I think that you are looking for something like:
SELECT m.id, m.name, d.lastvisit, d.info FROM master m, detail d WHERE m.id = d.id;
Jason
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 |
WedJun 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'
ifthey 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,NULLAS 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
At 23:47 +0300 on 02/06/1999, Ant9000 wrote: > 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? No, outer joins are not supported in PostgreSQL. Your solution is the accepted workaround, although I tend to recommend WHERE NOT EXISTS ( select * from detail WHERE detail.id = master.id )in the second clause - it is more efficient. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Wed, 02 Jun 1999, you wrote: >I think that you are looking for something like: > >SELECT m.id, m.name, d.lastvisit, d.info > FROM master m, detail d > WHERE m.id = d.id; > >Jason > No, that way I get an inner join: in my example the result would be 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 and I'm instead looking for this output: 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 | |
On Sat, 5 Jun 1999, Ant9000 wrote: > On Wed, 02 Jun 1999, you wrote: > >I think that you are looking for something like: > > > >SELECT m.id, m.name, d.lastvisit, d.info > > FROM master m, detail d > > WHERE m.id = d.id; > > > >Jason > > > > No, that way I get an inner join: in my example the result would be > > 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 > > and I'm instead looking for this output: > > 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 | | > Try this: SELECT m.id, m.name, d.lastvisit, d.info FROM master m, detail d WHERE m.id = d.id UNION ALL SELECT m.id, m.name, 0, 0 FROM master m WHERE not m.id=ANY(select id from detail);