X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CA6CD52A0B for ; Fri, 5 Aug 2005 17:23:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41457-05 for ; Fri, 5 Aug 2005 20:22:52 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 3BB325281F for ; Fri, 5 Aug 2005 17:22:51 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j75KMkSS039851 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 5 Aug 2005 14:22:50 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j75KMkM1098914; Fri, 5 Aug 2005 14:22:46 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j75KMkOP098913; Fri, 5 Aug 2005 14:22:46 -0600 (MDT) (envelope-from mfuhr) Date: Fri, 5 Aug 2005 14:22:46 -0600 From: Michael Fuhr To: D Kavan Cc: pgsql-admin@postgresql.org Subject: Re: outer joins Message-ID: <20050805202246.GA98821@winnie.fuhr.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/68 X-Sequence-Number: 18387 On Fri, Aug 05, 2005 at 03:55:16PM -0400, D Kavan wrote: > One the developers is saying that he can't do outer joins on postgresql. > Is this true? We have postgresql 8.02. No, that isn't true. PostgreSQL has supported outer joins for a long time. > He is using this syntax: > select from A left outer join b on A.id=B.id; > > This processes but comes back with a result like it was an inner join. Please show a complete example with table definitions, the actual query (the above fails due to a syntax error), the results, and the results you were expecting. Here's an example of an outer join: CREATE TABLE a (id integer, adata text); INSERT INTO a VALUES (1, 'a one'); INSERT INTO a VALUES (2, 'a two'); CREATE TABLE b (id integer, bdata text); INSERT INTO b VALUES (1, 'b one'); INSERT INTO b VALUES (3, 'b three'); SELECT a.id AS aid, a.adata, b.id AS bid, b.bdata FROM a LEFT OUTER JOIN b ON a.id = b.id; aid | adata | bid | bdata -----+-------+-----+------- 1 | a one | 1 | b one 2 | a two | | (2 rows) An inner join would give this: SELECT a.id AS aid, a.adata, b.id AS bid, b.bdata FROM a INNER JOIN b ON a.id = b.id; aid | adata | bid | bdata -----+-------+-----+------- 1 | a one | 1 | b one (1 row) -- Michael Fuhr