Re: outer joins

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