1

What is the difference between ColumnName = Null and ColumnName Is Null in SQL Server? I have researched about this but I didn't get any clarification.

Actually I want to retrieve column has null value

Select * from Table1 where column1 is null

This query returning expected result but the following query not giving

 Select * from Table1 where column1 = null
halfer
  • 19,824
  • 17
  • 99
  • 186
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • possible duplicate of [Why doesn't SQL support "= null" instead of "is null"?](http://stackoverflow.com/questions/7078837/why-doesnt-sql-support-null-instead-of-is-null). You should also check [Why doesn't SQL support “= null” instead of “is null”?](http://stackoverflow.com/questions/7078837/why-doesnt-sql-support-null-instead-of-is-null) – MarcinJuraszek Jan 19 '14 at 07:24

3 Answers3

3

Comparisons with null using normal operators are never true.

That's basically because null is "unknown" so it can't be determined whether it is or is not some value.

The special syntax is null and is not null must be used to determine nullness.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
2

Using comparison operators on NULL will result un Unkown result

Take a look at the following example:

Declare @temp table
(
 val1 int,
 val2 int 
)

INSERT INTO @temp
VALUES (1,NULL),
       (NULL,1),
       (NULL,NULL)

SELECT CASE 
        WHEN val1 > val2 THEN 'Val 1 greater'
        WHEN val2 > val1 THEN 'val 1 smaller'
        WHEN val1 = val2 THEN 'Val1 is equal to val 2'
        WHEN val1 = NULL THEN 'val1 = NULL'
        WHEN val1 IS NULL THEN 'val1 IS NULL'
        ELSE 'Unknown'
        END result 
FROM @temp

this will result:

Unknown
val1 IS NULL
val1 IS NULL

Please note that changing the ANSI_NULLS to OFF, will change the behaviour of the = operator, and will return TRUE for NULL = NULL

Any way, IMHO (and I think the prevailing view is), The best practice is using IS NULL

Avi Turner
  • 10,234
  • 7
  • 48
  • 75
-2

"is null" is the proper way. It's like the difference between <> and != for not equal. != is the standard.

here is the Microsoft documentation: http://technet.microsoft.com/en-us/library/ms188795.aspx

"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."

Johnny
  • 1,141
  • 9
  • 6