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.