From: Steve Crawford <scrawford@pinpointresearch.com>
To: Stefan Weiss <spaceman@foo.at>
To: pgsql-sql@postgresql.org
Subject: Re: "=" operator vs. "IS"
Date: Mon, 28 Jun 2004 15:27:14 -0700
Message-ID: <200406281527.14116.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <200406290002.29099.spaceman@foo.at>
References: <200406290002.29099.spaceman@foo.at>
> 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?
You've got it. NULL is the _absence_ of a known value so any
comparison or operation on it yields an unknown result.
So why can't you use = NULL?
Consider the a list of names and ages where Jack's and Jill's ages are
null. Now we run a query to list people who are of the same age.
Should Jack and Jill be listed as being the same age? Of course not.
You can't compare whether the two unknown values are equal any more
than you could determine whether or not they are over 18.
The SQL spec and PostgreSQL properly use and enforce this
interpretation of NULL.
The correct way to ask your questions is ...where foo.bar is null...
Cheers,
Steve
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: scrawford@pinpointresearch.com, spaceman@foo.at
Subject: Re: "=" operator vs. "IS"
In-Reply-To: <200406281527.14116.scrawford@pinpointresearch.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