NULL value self comparison in a table

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

-- Create table t and insert values.  
use tempdb
CREATE TABLE dbo.t (a INT NULL);  
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);  
GO  
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
go
-- 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.

StackExchange Database Administrators Q#148048, answer score: 13