4

I would consider SET ANSI_NULLS OFF mode very convenient and would use it by default if it worked consistently - allow to compare nulls using = or != everywhere. But it works only if if I compare to explicit inline null, but if I compare to a field that contains null - it DOES NOT WORK. Why would Microsoft design it like that ? No wonder why nobody uses SET ANSI_NULLS OFF mode by default. I'd rather stay with pain of "is null" and "is not null" than think about what I use inline and what not.

Here is example that shows the problem - line with ??? should return 2 rows, but it returns only one row.

CREATE TABLE t1 (a int null, b int null)
INSERT INTO t1 values (null,null)
INSERT INTO t1 values (0,null)
INSERT INTO t1 values (null,0)
INSERT INTO t1 values (0,0)
set ansi_nulls ON
select * from t1 where a=null -- empty result
select * from t1 where a=b  -- (0,0)
select * from t1 where null=null -- empty result
set ansi_nulls OFF
select * from t1 where a=null -- (null,null),(null,0)
select * from t1 where a=b -- why only (0,0) ???, it should be (0,0),(null,null)
select * from t1 where null=null -- returns all 4 rows
drop table t1
alpav
  • 2,972
  • 3
  • 37
  • 47

1 Answers1

6

This was answered here:

How does ANSI_NULLS work in TSQL?

set ansi_nulls off only affects the comparision if one of the operands is a null variable or a literal null.

Furthermore, you should not write any new code using the off setting, as it will not be supported in the future:

http://msdn.microsoft.com/en-us/library/ms188048.aspx

Update:

After re-reading your question, I think you are effectively saying why doesn't:

select * from t1 where a=b

function like this:

select * from t1 where a=b or (a=null and b=null)

which will return (null, null), (0,0) in your example.

So, you are checking for two conditions:

  1. Both column values are equal
  2. Both column values are unknown

When your expression is written as a=null, it is pretty clear what you are trying to do. But if written as a=b, are you intending to check for only the 1st condition, or both?

Another way to think about, say you are writing a query to return all customers that have the same home + mobile number. Would you want to return customers that had null values in both these columns?

So in short, MSFT doesn't want to automatically check for two conditions when only one is stated - I think this is the right design.

Community
  • 1
  • 1
Brian Vander Plaats
  • 2,257
  • 24
  • 28
  • 3
    The OP knows how it works. They are asking **why** it was implemented that way. – Martin Smith Feb 11 '12 at 11:08
  • @Brian Vander Plaats: in JavaScript (language that MSFT did not design) which happened to be much better designed than SQL when I put a==b I get true even if a==undefined and b==undefined. Why SQL have to be different than JavaScript ? – alpav Mar 08 '12 at 21:57
  • @alpav note that in C# as well, null == null is true. I guess MSFT could have gone this way with SQL Server, but this would not be standards compliant. See the 3VL section here: http://en.wikipedia.org/wiki/SQL – Brian Vander Plaats Mar 15 '12 at 15:54
  • 1
    @Brian Vander Plaats: ANSI standard is not ideal too, but I thought that SET ANSI_NULLS OFF establishes MSFT own standard and if they went that far to implement f==null then why not do it right (like in C#) ? Why SQL treatment of nulls have to be exception from other languages ? – alpav Mar 21 '12 at 16:22
  • @Brian: now that I think about it, MSFT tried to make C# convenient for C++ programmers, so probably this feature of C# was not really designed by MSFT. – alpav Mar 28 '12 at 18:03
  • 2
    Their feature is broken. Period. That's why they are deprecating an eliminting it. Unfortunately, the ISO standard sucks, and Microsofts way of having ANSI_NULLs OFF is much better, but because of that obvious glitch where a=b doesn't work as expected when both null, they are forced to eliminate the feature. I wish they'd just fix it, and keep the feature. There's no reason anyone should have to write special queries to replace "=" with "is" if and only if a value involved in the comparison is null. What a pain. – Triynko Oct 16 '13 at 17:40