Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA09678 for ; Sun, 6 Jun 1999 08:05:22 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id EDFE84B514; Sun, 6 Jun 1999 15:05:20 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA1BC0; Sun, 6 Jun 1999 15:07:55 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <99060222482601.00588@chomp> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Sun, 6 Jun 1999 15:06:14 +0300 To: Ant9000 , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] OUTER JOINs in PostgreSQL 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