From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Zoltan Boszormenyi <zboszor@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN problem
Date: Tue, 29 Jun 2004 14:25:18 -0700 (PDT)
Message-ID: <20040629142001.T68031@megazone.bigpanda.com> (raw)
In-Reply-To: <40D9DD8F.50306@freemail.hu>
References: <40D9DD8F.50306@freemail.hu>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, zboszor@freemail.hu
Subject: Re: OUTER JOIN problem
In-Reply-To: <20040629142001.T68031@megazone.bigpanda.com>
* 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