Обсуждение: "=" operator vs. "IS"
Hi. 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? If it's the "unknown" part, then why can't I use "=" to compare with TRUE or FALSE? cheers, stefan
> 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
On June 28, 2004 03:02 pm, Stefan Weiss wrote: > 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 far as I have been able to tell, it is one of those quirks about SQL that you shouldn't bother trying to understand. It just IS. <rimshot/> -- Michael A. Nachbaur <mike@nachbaur.com> http://nachbaur.com/pgpkey.asc
Think about a join where you do something like: t1.f1 = t2.f1 If both columns were blank, would you want the join to succeed? Probably not, if you did, then you would potentially have a cartesian select. This is not a good explanation of NULL non-equality, but I thought it might be useful. NOTE: A related topic is OUTER JOIN's which is how the above join would properly be implemented Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael A Nachbaur > Sent: Monday, June 28, 2004 6:28 PM > To: Stefan Weiss > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] "=" operator vs. "IS" > > > On June 28, 2004 03:02 pm, Stefan Weiss wrote: > > 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 far as I have been able to tell, it is one of those quirks > about SQL that > you shouldn't bother trying to understand. It just IS. <rimshot/> > > -- > Michael A. Nachbaur <mike@nachbaur.com> > http://nachbaur.com/pgpkey.asc > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >