I am always puzzled regarding some mysterious t-sql behavior, like the following

-- Create table t and insert values.  
use tempdb
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);  
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
-- expect 3 rows returned but only 2 returned (without null value row)
select * from dbo.t where a = a 

This is not about how to retrieve all rows in a table and also not about avoiding use of ANSI_NULLS.

I just want to solicit some insights why t-sql behaves like this.


This is a surprising behaviour but from MSDN page, SET ANSI_NULLS, we can at least know that is the expected behaviour. One more reason to never use ANSI_NULLS OFF:

SET ANSI_NULLS affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

| improve this answer | |

While it may not be crystal clear from the msdn documentation, I believe you will find the following true

"SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison."

See this https://stackoverflow.com/questions/2866714/how-does-ansi-nulls-work-in-tsql

| improve this answer | |
  • Thanks Scott and ypercube, both your answers are to the points for this behavior, so I upvote both your answers. – jyao Aug 26 '16 at 20:00
  • Ypercube was first:) – Scott Hodgin Aug 26 '16 at 20:01

Robert Sheldon in the following post from 2015 discusses NULL behaviors and why they sometimes (but not always) fail


He describes 13 NULL failures that a programmer can easily trip over.

Failure #1: Not knowing what NULL means

Explanation: NULL is a non-value, a nonexistent value. It is not zero. It is not an empty string. A value cannot equal NULL. No two NULL values are equal.

That is the basic problem, but be sure to read about the other failures.

Yes, earlier versions (pre-SQL Server 7 I believe) behaved differently, more like what you are wanting.

However, if you search for the issue on Stack Overflow and Stack Exchange you will find many long threads discussing the issues.

| improve this answer | |
  • 3
    I once read the linked post from Robert Sheldon, but it does not (IMHO) have any theory or evidence that explain the behavior of my example. – jyao Aug 26 '16 at 19:46
  • 1
    "No two NULL values are equal." OK but even the people who know that would expect the opposite when ansi nulls is set to off. Especially because WHERE NULL = NULL yields true when the setting is of. – ypercubeᵀᴹ Aug 26 '16 at 21:19

To add to the discussion, the SQL92 standard's definition of NULL can be interpreted ambiguously. Here's a good summary of NULL handling and interpretation from various DBMSes courtesy of sqlite.org.

DISCLOSURE: I kinda remember reading about SQL92's "ambiguity" from an older version (like 6-8 years ago) of the sqlite.org page linked above, but that page has been updated since.

RLF's answer above has a good quote, but if I disagree with Robert Sheldon it's only because I consider "something that does not exist" (i.e. a NULL) to be philosophically and English-language-semantically equivalent to "something else that does not exist". If I am to understand Sheldon's logic, then one could declare the definition of NULL is also NULL. (If it doesn't exist, then how can we define it? Creepy, huh?)

I see a variation of Russell's Paradox brewing (and a headache). :-\

But again, this is a discussion on English language semantics (NOT SQL) and the philosophy debate belongs over here. :-)

| improve this answer | |
  • P.S. I'm new here to this SE community; if this has been discussed ad nauseum before, then I apologize. – pr1268 Aug 27 '16 at 4:49
  • 1
    Where exactly is the ambiguity in the standard? – ypercubeᵀᴹ Aug 27 '16 at 10:42
  • @ypercubeᵀᴹ: I believe the ambiguity lay in the attempt to "fit" a 3VL into a Boolean. Table joins with NULL comparisons could be interpreted in several different ways. – pr1268 Aug 30 '16 at 2:35
  • I would agree about inconsistency but not about ambiguity. – ypercubeᵀᴹ Aug 30 '16 at 7:59
  • 1
    @ypercubeᵀᴹ: Fair enough... I was only quoting what an older version of the sqlite.org page I linked above said ("SQL92 is ambiguous with regards to NULL handling and interpretation" or something very similar). But I don't mean to argue. Perhaps the sqlite.org page was itself misleading and/or all-out incorrect. Which likely explains why it was updated. – pr1268 Aug 30 '16 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.