Re: Difference between = and IN

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/07/05 

Date: Mon, 07 Feb 2005 15:41:29 +0100

On Mon, 7 Feb 2005 06:17:04 -0800, Andy wrote:

>Hi Hugo
>
>Thanks for this - I wasn't aware there was an issue with IN/NOT IN and
>Nulls! I was under the impression that Nulls would just be excluded from the
>results.

Hi Andy,

That's what most people think - but it's not true.

WHERE Column1 NOT IN (1, 2, NULL)

will (as per the ANSI-standard SQL-92) be equivalent to

WHERE Column1 <> 1
AND Column1 <> 2
AND Column1 <> NULL

And since Column1 <> NULL is neither "true" nor "false", but "unknown",
this expression can only evaulate to "false" (as a result of "false" AND
"unknown") or "unknown" (as a result of "true" and "unknown").

Consider it like this - would you be willing to bet a significant amount
that the 40 is NOT IN (your age, George W. Bush's age, my age)?

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: 3vl 2vl and NULL
    ... > And even if you remodel the DB to get rid of the "inapplicable NULLs", ... > whatever other reasons there might be. ... >>- My family member Aunt Marge has an age of 47 years. ...
    (comp.databases.theory)
  • Re: 3vl 2vl and NULL
    ... > I object to the choice of words "It is unknown", because in fact Uncle ... age, even if nobody knows it. ... > number of NULLs to denote the various reasons why data can be missing. ...
    (comp.databases.theory)
  • Re: TSQL INNER JOINS and null fields
    ... NULL can be best thought of as "Unknown". ... Name Age Job ... Paul Programmer ... A better solution is to include the possiblity of NULLs in the query. ...
    (microsoft.public.sqlserver.programming)
  • Re: Difference between = and IN
    ... I misunderstood what you meant in the previous post. ... strange results if Nulls were present in the records whereas NOT ... "Hugo Kornelis" wrote: ... > Hi Andy, ...
    (microsoft.public.sqlserver.mseq)
  • Re: sql question
    ... I've done it via function call from sql, but how do I append another 5 ... NULLs to it? ... steph wrote: ...
    (comp.databases.oracle.server)