Re: outer joins

If he's using a restriction upon an outer joined table in the where clause
it might be returning results like it was an inner join, ie:

select * from A left outer join B on A.id=B.id
where B.accessed>'2005-10-31';

Instead you'd want to check for the outer joined field having a null
value in the where clause, or move that restriction up to the on clause:

select * from A left outer join B
  on A.id=B.id AND B.accessed>'2005-10-31';

                 or

select * from A left outer join B
  on A.id=B.id
where (B.accessed IS NULL OR B.accessed>'2005-10-31');

I assure you, outer joins work just fine on PostgreSQL.

Jason

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 2:55 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] outer joins


Hi,

One the developers is saying that he can't do outer joins on postgresql.
Is this true?  We have postgresql 8.02.

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.

~DjK



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq