Received: from ns.seldati.it (ns.seldati.it [195.120.220.2]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA21030 for ; Wed, 2 Jun 1999 16:48:15 -0400 (EDT) (envelope-from ant9000@seldati.it) Received: from chomp (ant9000@pppTn3.seldati.it [195.120.220.239]) by ns.seldati.it (8.8.7/8.8.7) with SMTP id WAA13486 for ; Wed, 2 Jun 1999 22:46:39 GMT From: Ant9000 To: pgsql-sql@postgreSQL.org Subject: OUTER JOINs in PostgreSQL Date: Wed, 2 Jun 1999 22:47:09 +0200 X-Mailer: KMail [version 1.0.17] Content-Type: text/plain MIME-Version: 1.0 Message-Id: <99060222482601.00588@chomp> Content-Transfer-Encoding: 8bit X-KMail-Mark: 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