pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: D Kavan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: outer joins
Date: Fri, 5 Aug 2005 14:22:46 -0600
Message-ID: <20050805202246.GA98821@winnie.fuhr.org> (raw)
In-Reply-To: <BAY102-F39DA7AE2F13E5FA686EA11D1C70@phx.gbl>
References: <BAY102-F39DA7AE2F13E5FA686EA11D1C70@phx.gbl>

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



view thread (11+ messages)  latest in thread

Message-ID: <20050805202246.GA98821@winnie.fuhr.org>
Permalink:  ../20050805202246.GA98821@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050805202246.GA98821@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, bitsandbytes88@hotmail.com
  Subject: Re: outer joins
  In-Reply-To: <20050805202246.GA98821@winnie.fuhr.org>
  References: <BAY102-F39DA7AE2F13E5FA686EA11D1C70@phx.gbl> <20050805202246.GA98821@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox