Re: Difference between = and IN

From: Andy (Andy_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 07:19:02 -0800

Hi Hugo

I misunderstood what you meant in the previous post. I have worked out in
the past that you have to use 'IS [NOT] NULL' rather than '<> Null' (or 'IN
Null' or '= Null'). I thought you meant that NOT IN (1,2) would return
strange results if Nulls were present in the records whereas (I believe) NOT
IN (1,2) would return all rows that did not contain a 1, 2 or Null.

Its good to know why NOT IN (1,2,NULL) doesn't work though - I should
probably try reading the ANSI standard...

Thanks very much

Andy

"Hugo Kornelis" wrote:

> 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: Space required for an empty varchar field?
    ... >who don't understand how to deal with NULLS. ... as they'll be in the result set of an outer join. ... it gets unwieldy fast if you have multiplle NULLable columns. ... Thanks Hugo. ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Help with IF stmt
    ... Hi Andy, ... I suspect that the EmpName column in reality won't hold ... Some rows of sample data. ... Best, Hugo ...
    (microsoft.public.sqlserver.mseq)
  • Re: Difference between = and IN
    ... That's a relief I didn't fancy having to re-check all my databases!!! ... also nice to know I'm not a complete eejit! ... "Hugo Kornelis" wrote: ... > Hi Andy, ...
    (microsoft.public.sqlserver.mseq)
  • Re: Difference between = and IN
    ... I was under the impression that Nulls would just be excluded from the ... Hi Andy, ... AND Column1 NULL ... that the 40 is NOT IN (your age, George W. Bush's age, my age)? ...
    (microsoft.public.sqlserver.mseq)
  • Re: Complex query with IN
    ... >>but that is not valid SQL. ... > Hi Andy, ... > FROM foo ... > Best, Hugo ...
    (microsoft.public.sqlserver.msde)