Sql-server – Handling NULL in SQL Server

sql-server-2008-r2

I am facing issue when trying to handle null.

Here is example

I have a table

Create table Test_Table 
(
id int null,
pName varchar(10)
)
GO

Id column has NULL value.

INSERT INTO Test_Table 
SELECT NULL, 'A' UNION 
SELECT NULL,'B' UNION 
SELECT NULL, 'C' UNION 
SELECT NULL,'D' 
GO

Now when I execute different select statements, last two conditions are failing. I can use ISNULL to handle it but there are approx 500 SPs which I would need to change.

So, I need solution apart from that, if possible.

SELECT * FROM Test_Table WHERE id is NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=NULL --True


SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id --False. Why???

Declare @ID INT = 0

SET ANSI_NULLS OFF
Select case when @ID > 0 then @ID else id end as Statements, * 
From   Test_Table  
Where  ID = case when @ID > 0 then @ID else ID END -- Fails. Why??

When Id=NULL is true then why Id=Id is false??

Thanks!

Best Answer

Have a look at remarks on MS Docs about IS NULL:

Remarks
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=).
Comparison operators return UNKNOWN when either or both arguments are NULL.

And at SET ANSI_NULLS MS Docs:

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.

Let me add some other records to your table:

INSERT INTO Test_Table 
VALUES (NULL, 'A'),(NULL,'B'),(NULL,'C'),(NULL,'D'),(1,'E'),(2,'F');

Now, your query:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id;

Returns:

id | pName
-: | :----
 1 | E    
 2 | F

And the same for your second query:

Declare @ID INT = NULL

SET ANSI_NULLS OFF
SELECT CASE WHEN @ID > 0 THEN @ID ELSE id END AS Statements, * 
FROM   Test_Table  
WHERE  ID = CASE WHEN @ID > 0 THEN @ID ELSE ID END
GO

Statements | id | pName
---------: | -: | :----
         1 |  1 | E    
         2 |  2 | F

Null indicates lack of value and you must explicitly check for NULL values using IS NULL:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id IS NULL;

dbfiddle here

Related Question