2

I wasn't getting any results where I expected them and long story short I found this odd behavior:

select 1 where 'a' in ('a', 'b', null)
 -> 1

select 1 where 'a' NOT in ('b', 'c', null)
 -> empty resultset

I'm relatively new to SQL Server (coming from MySQL) but this seems a bug to me, is this expected/desired behavior? I'm aware of the anything + null == null paradigm, but if that's the explanation, then I would expect the first query to return an empty resultset just the same.

I also tried:

select 1 where NOT ('a' in ('b', 'c', null))

again an empty resultset, I'm confused now...

I added where [mycolumn] is not null to my original query, problem solved, but I'm just curious.

Edit

I see from the duplicate marking that this depends on the setting of ansi_nulls. When this is on, null is treated as unknown. And yes I can see that you cannot say with certainty that any value is absent in a collection that has an unknown value.

Why would you want to regard null as unknown though? They are very different concepts to me.

null means there is no value, like asking what's in the [gum] drawer to someone whose cabinet doesn't have a [gum] drawer. unknown means there IS a value, but for some reason we don't have access to that value. Maybe the [gum] drawer is locked, it might have content, it might be empty, but it's not null, it's unknown.

Where is my thinking off with this?

asontu
  • 4,548
  • 1
  • 21
  • 29
  • 2
    NOT IN => 'a' <> 'b' AND 'a' <> 'c' AND 'a' <> NULL => True AND True AND NULL => NULL – adrianm Feb 25 '14 at 15:21
  • The behavior of NULLs is defined in the ANSI SQL standard. I'm pretty sure MySQL and SQL Server have the same rules. – Gordon Linoff Feb 25 '14 at 15:28
  • 2
    I have three people. I know the first name of one of them is `David`, the second one's name is `Tom`, and I don't yet know the name of the third person. In that set of three people, is it definitely known that *none* of them have the name `Stephanie`? – Damien_The_Unbeliever Feb 25 '14 at 15:36
  • @Damien_The_Unbeliever you're implying sql server treats `null` as `unknown`. To me, `null` means that the value is absent. Like a newborn baby without a given name yet. She might be called `Stephanie` in the future, but I'm not querying the database in the future, I'm querying RIGHT NOW. `Tom` might change his name to `Stephanie` in the future too, that doesn't mean I want the query to suggest this _might_ happen when I run it :P – asontu Feb 25 '14 at 15:45
  • The issue is that, in SQL, `null` has, at times, both some of the `unknown` nature, and some of the `inapplicable` nature (and possibly others you may be thinking of). Unfortunately, the SQL language is already pretty set in this mindset (although it's also worth pointing out that SQL actually has a concept *called* `UNKNOWN`, which is the third truth value produced when there's a `NULL` input to a predicate that can't be eliminated. so `'a' NOT in ('b', 'c', null)` actually evaluates as `UNKNOWN` (rather than `FALSE`). – Damien_The_Unbeliever Feb 25 '14 at 15:52
  • If MySQL returned anything from the second statement, that is a but in **MySQL** –  Feb 25 '14 at 17:53
  • 1
    You might also find [Codd's Rules](http://en.wikipedia.org/wiki/Codd's_12_rules) interesting reading. Those were the basis behind the thinking for SQL. And note, again, that in this case, `null` was being considered at different times as "there is no value" and "we do not know the value". Codd later proposed alternatives that involved multiple types of `null`s, where one would be `not applicable`, one would be `not known`, etc, but SQL is insane enough with just one `null`. **You** may want `null` to only mean the (proven) absence of a value, but that's not the line that SQL took. – Damien_The_Unbeliever Feb 25 '14 at 18:32

0 Answers0