Message-ID: <41955aeb655d4b2976f0ae6878b95fa6@news.teranews.com>
Date: Thu, 01 Jul 2004 15:02:25 GMT
Lines: 33
From: Jeff Boes <jboes@qtm.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7) Gecko/20040616
MIME-Version: 1.0
Subject: Re: FW: "=" operator vs. "IS"
References: 
 <7A4ADADFC8AFF0478D47F63BEDD57CE33A320C@gpmail.gphq.genpathpharma.com>
In-Reply-To: 
 <7A4ADADFC8AFF0478D47F63BEDD57CE33A320C@gpmail.gphq.genpathpharma.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org


> 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