Received: from news.tht.net (news.hub.org [216.126.91.242]) by hub.org (8.10.1/8.10.1) with ESMTP id e7V7DWC04236 for ; Thu, 31 Aug 2000 03:13:32 -0400 (EDT) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id CAA42929 for pgsql-sql@postgresql.org; Thu, 31 Aug 2000 02:54:48 -0400 (EDT) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f From: "Alexandru COSTIN" X-Newsgroups: comp.databases.postgresql.sql References: Subject: Re: Outer join statement ? Lines: 39 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Message-ID: Date: Thu, 31 Aug 2000 06:54:46 GMT X-Complaints-To: noc@Romania.EU.net X-Trace: nreader1.kpnqwest.net 967704886 193.226.136.126 (Thu, 31 Aug 2000 08:54:46 MET DST) Organization: KPNQwest customer news service To: pgsql-sql@postgresql.org X-Archive-Number: 200008/350 Hello, There are 2 ways of doing outer joins, both are relatively expensive from the CPU time point of view 1. Create a function that returns the ojined value, or null if the value does not exists in the secod table 2. Use a union beetwen a left join and a straight select with a where condition. Next there's an example inspired from the Adison Westley book (I suggest you grab a copy from the postgresql site. (/docs) select customer.name,salesorder.order_id from customer join salesorder on customer.cust_id=salesorder.fk_cust_id union all select customer.name,null::int4 from customer where customer.cust_id not in (select fk_cust_id from salesorder); Probably an improvement here is to use exists on the second select, but I didn't have the time to document. Anyway, this will not work embedded in a view ("union all" is not supported in views). Alexander "Nasdaq" wrote in message news:fb-3008002344340001@du-211-67.lyon.dialup.freesurf.fr... > The statement "Outer Join" is not available in Postgres ? > Is there an extension provided by a third company that implement this > functionnality ? > > If not do you know when this functionnality will be available in > Postgres ? > > Thank > > Fred > > -- > TechBourse : le premier site en FRANCAIS dédié le NASDAQ > http://www.techbourse.com