Date: Tue, 29 Jun 2004 14:25:18 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Zoltan Boszormenyi <zboszor@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN problem
In-Reply-To: <40D9DD8F.50306@freemail.hu>
Message-ID: <20040629142001.T68031@megazone.bigpanda.com>
References: <40D9DD8F.50306@freemail.hu>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 23 Jun 2004, Zoltan Boszormenyi wrote:

> I don't know how PostgreSQL works internally but this bug *must* be
> conforming to some standard if two distinct SQL server products behave
> (almost) the same. I said almost, I discovered the same annoyance today
> on an Informix 9.21 running under SCO UNIX on a slightly larger dataset
> with less than 70 rows. It just left out some arbitrary rows that had
> NULLs from the right side table (i.e not existing rows).

It's not a bug.  The outerness of the join is defined over the join
conditions (the ON portion), not the where conditions.  In the first case
you got rows from the join that matched the join condition like (a.i=2,
c.a=1, c.b=2) which the where then filtered out.

I think
 select * from a left outer join c on (a.i=c.a and c.b=1)
meets the standard and may give you what you want.