From: Jeff Boes <jboes@qtm.net>
To: pgsql-sql@postgresql.org
Subject: Re: FW: "=" operator vs. "IS"
Date: Thu, 01 Jul 2004 15:02:25 GMT
Message-ID: <41955aeb655d4b2976f0ae6878b95fa6@news.teranews.com> (raw)
In-Reply-To: <7A4ADADFC8AFF0478D47F63BEDD57CE33A320C@gpmail.gphq.genpathpharma.com>
References: <7A4ADADFC8AFF0478D47F63BEDD57CE33A320C@gpmail.gphq.genpathpharma.com>
> I'm just curious - why is it not possible to use the "=" operator to
> compare values with NULL? I suspect that the SQL standard specified it
> that way, but I can't see any ambiguity in an expression like "AND
> foo.bar = NULL". Is it because NULL does not "equal" any value, and the
> expression should be read as "foo.bar is unknown"? Or is there something
> else I'm missing?
As noted elsewhere, joining two tables on "a.foo = b.foo" where both foo
values are NULL is not usually what you want.
But if you really, truly do want that, then you always have this:
coalesce(a.foo,0) = coalesce(b.foo,0)
or a similar construct, using something in place of zero that has the
same base type as a.foo and b.foo, and which doesn't occur in either table.
(Why? Because you really don't want
a.foo = coalesce(b.foo,0)
or
b.foo = coalesce(a.foo,0)
to give you false positives.)
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jboes@qtm.net
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: jboes@qtm.net
Subject: Re: FW: "=" operator vs. "IS"
In-Reply-To: <41955aeb655d4b2976f0ae6878b95fa6@news.teranews.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