NULL = NULL

One of my friend had discussion with me some days back that NULL is not equal to another NULL...! 

Is that TRUE ?

I said that "NO, IT IS NOT ALWAYS...!"

What that means.. "NOT ALWAYS" ?

In SQL Server, we have SET option called "ANSI_NULLS"

Is there any way to identify that what are all the options have been set to ON ?

DBCC USEROPTIONS
GO


Its a session specific option. So, It will list out What are all the user options have been set to ON along with some other options!

OK.

NULL is NOT EQUAL to another NULL. How ? 


When ANSI_NULLS is set to ON

SET ANSI_NULLS ON
GO


IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO


Result : NOT EQUAL

 

NULL is EQUAL to another NULL. How ? 

When ANSI_NULLS is set to OFF

SET ANSI_NULLS OFF
GO

IF
(NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO

Result : EQUAL


So, NULL is not equal to another NULL - NOT ALWAYS, It depends on ANSI_NULLS option!

3 comments:

  1. Very nice and useful finding. We may fall unknowingly like these pitfalls. In addition, when I check the same with IS NULL, it gives correct answer on either ON or OFF states for ANSI_NULL setting. Keep posting. :)

    ReplyDelete
  2. Thank You San!

    YES.

    IS NULL always work regardless of ON / OFF

    ReplyDelete
  3. Hello Pandian,

    How does a Unique Constraint treat a NULL? More than one Null values are not allowed in Unique Column just because NULL is equal to NULL

    ReplyDelete