CODEWITHSUNDEEP
sql-serverisnull
Developer Webs
Developer Webs

Reputation: 1021

ISNULL erroneous return?

I have the following query:

SELECT apps.Field4,
       ISNULL(apps.field4, '-1')
FROM   applications apps
WHERE  apps.OBJECT_ID = 1727847
       AND ISNULL(apps.Field4, -1) = -1

apps.field4 is an integer, and no record has a value less than 0 for field4.

The return values for the above query are: 

+------+----+
| NULL | -1 |
+------+----+

But if I add AND apps.field4 is NULL to the where clause, no records are returned:

SELECT apps.Field4,
       ISNULL(apps.field4, '-1')
FROM   applications apps
WHERE  apps.OBJECT_ID = 1727847
       AND apps.field4 IS NULL
       AND ISNULL(apps.Field4, -1) = -1

Why does ISNULL seem to correctly identify the NULL value, but IS NULL does not?

Upvotes: 4

Views: 234

Answers (2)

Developer Webs
Developer Webs

Reputation: 1021

It looks like database corruption was the issue. In the end I'd either used DBCC CHECKDB or DBCC CHECKTABLE to fix the issue, and the issue was gone.

Upvotes: 0

Mincong Huang
Mincong Huang

Reputation: 5552

I know that I'm not answering your question. However, I found something interesting in MSDN :

C. Testing for NULL in a WHERE clause Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.

USE AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO

https://msdn.microsoft.com/en-us/library/ms184325.aspx

Upvotes: 1

Related Questions