Thread: AW: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. S tand ards
> > Actually I am not sure whether the column = NULL syntax is even defined > > or allowed in SQL92 (e.g. Informix interprets the NULL as column name in > > this context and errs out). > > I don't have the standard handy, but I do have Joe Celko's book, "Data > & Databases: Concepts in Practice". He says (in section 8.2, under > the heading "Multivalued Logic"): > > A NULL cannot be compared to another NULL or to a value I mean that I am not sure NULL is a valid constant in a where clause comparison. Thus it could be, that NULL in "where column = NULL" is not defined to have a special meaning according to SQL92. NULL is probably only defined in a special context, like:IS NULLIS NOT NULL Andreas
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > Thus it could be, that NULL in "where column = NULL" is not defined > to have a special meaning according to SQL92. The way I interpret Celko's interpretation of SQL92, that specific construct has a meaning; it evaluates to UNKNOWN, thus not to TRUE, and the WHERE clause becomes useless, as does any other combination of a theta operator and the explicit constant 'NULL'. This is almost, but not quite, an argument for allowing "= NULL" for "IS NULL". ;-) Does anyone out there have the actual text of the standard? -tih -- The basic difference is this: hackers build things, crackers break them.
On Thu, Jun 07, 2001 at 02:46:50PM +0200, Tom Ivar Helbekkmo wrote: > Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > > > Thus it could be, that NULL in "where column = NULL" is not defined > > to have a special meaning according to SQL92. > > The way I interpret Celko's interpretation of SQL92, that specific > construct has a meaning; it evaluates to UNKNOWN, thus not to TRUE, > and the WHERE clause becomes useless, as does any other combination of > a theta operator and the explicit constant 'NULL'. This is almost, > but not quite, an argument for allowing "= NULL" for "IS NULL". ;-) > > Does anyone out there have the actual text of the standard? > > -tih I dont know the standard for that, but to add an experience in another server (Interbase), '= null' has no meaning in Interbase, ie, doesnt works as 'IS NULL'. Sergio Bruder -- ( )) (tm) http://sergio.bruder.net |""|-. http://pontobr.org |__|-' bruder@conectiva.com.br, sergio@bruder.net ------------------------------------------------------------------------------ pub 1024D/0C7D9F49 2000-05-26 Sergio Devojno Bruder <bruder@conectiva.com.br> Key fingerprint = 983F DBDF FB53 FE55 87DF 71CA 6B01 5E44 0C7D 9F49 sub 1024g/138DF93D 2000-05-26