CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
AshutoshPujari
AshutoshPujari

Reputation: 146

NULL comparison in SQL server 2008

I know that in SQL when we compare two NULL values, result is always false. Hence, statements like 

SELECT case when NULL = NULL then '1' else '0' end

will always print '0'. My question is how functions like ISNULL determine whether value is null or not. Because, as per my understanding (and explained in above query) comparison of two null values is always FALSE.

Upvotes: 0

Views: 2065

Answers (6)

Sarthak Grover
Sarthak Grover

Reputation: 121

I hope it works

SELECT CASE WHEN ISNULL(NULL,NULL) = NULL THEN 1 ELSE 0 END

Upvotes: 0

Adi
Adi

Reputation: 232

SELECT case when  'NULL' = 'NULL' then '1' else '0' end


SELECT case when   isnull(columnname,'NULL')='NULL' then '1' else '0' end



SET ANSI_NULLS OFF 

SELECT case when NULL = NULL then '1' else '0' end

Upvotes: -2

Paul Maxwell
Paul Maxwell

Reputation: 35553

... or {2} are you literally asking how the ISNULL function in SQL Server itself works?

Actually I am asking for the second{2}. How ISNULL function in SQL server works. If comparison of two nulls is not defined/unknown then how isnull function compares two null values to return appropriate results?

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. ... NULL (SQL)

ISNULL ( check_expression , replacement_value ) is not concerned with comparison of values at all. It is concerned purely with the existence of value in the first parameter.

It tests if the check_expression has any value. If it does have any value that value is returned. If check_expression has no value the ISNULL function returns the second parameter replacement_value.

It does NOT compare the two values. It tests forthe existence of value in the first parameter only.

Upvotes: 1

Adrian Wragg
Adrian Wragg

Reputation: 7401

Your initial assumption appears to be that ISNULL is an alias for existing functionality which can be implemented directly within SQL statements, in the same way that a SQL function can. You are then asking how that function works.

This is an incorrect starting point, hence the confusion. Instead, like similar commands such as IN and LIKE, ISNULL is parsed and run within the database engine itself; its actual implementation is most likely written in C.

If you really want to look into the details of the implementation, you could take a look instead at mySQL - it's open source, so you may be able to search through the code to see how ISNULL is implemented there. They even provide a guided tour of the code if required.

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You need to set the set ansi_nulls off and then check your result. Null can be thought of as an unknown value and when you are comparing two unknown values then you will get the result as false only. The comparisons null = null is undefined.

set ansi_nulls off
SELECT case when NULL = NULL then '1' else '0' end

Result:-

1

From MSDN

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

As correctly pointed by Damien in comments the behavior of NULL = NULL is unknown or undefined.

Upvotes: 4

Navneet
Navneet

Reputation: 447

set ansi_nulls off

SELECT case when NULL = NULL then '1' else '0' end

result=1

set ansi_nulls on
SELECT case when NULL = NULL then '1' else '0' end

result=0


so that is the difference

Upvotes: 0

Related Questions