Re: outer joins

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: D Kavan <bitsandbytes88(at)hotmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: outer joins
Date: 2005-08-05 20:22:46
Message-ID: 20050805202246.GA98821@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message D Kavan 2005-08-05 20:31:41 Re: outer joins
Previous Message Jason Minion 2005-08-05 20:14:20 Re: outer joins