Handling NULLs in SQL query

Question

What is the right practice of checking NULLs in SQL Case ?

1) Using ISNULL()

WHEN (ISNULL(TABLE.COLUMN,0) > 0) THEN ....

2) Using IS NULL

WHEN TABLE.COLUMN IS NOT NULL THEN ....

Possible duplicate: http://stackoverflow.com/questions/3118213/isnull-vs-is-null — Marek Dzikiewicz, Jul 23 '12 at 06:59

score 1 · Answer 1 · answered Jul 23 '12 at 06:57

1

Second one is right if you want to check for null value in SQL case..

answered Jul 23 '12 at 06:57

Ram Singh

6,664
35
100
166

score 1 · Accepted Answer · answered Jul 23 '12 at 09:59

1

If you are checking any condition then always use 'is null' and if replacing any value with a different one, then use isnull(a,b). Check the following - http://msdn.microsoft.com/en-us/library/ms184325.aspx

Read the last line specially!!!

answered Jul 23 '12 at 09:59

Ashwini Mohan

157
7

Vishwanath Dalvi · Answer 3 · 2012-07-23T08:43:17.800

0

This is also another approach to check for NON NULL values.

Checking for length of the column if it is greater than 1 or equal 1 then its a NON NULL value.

declare @emp table
 (
 fname varchar(50)
 );

 INSERT into @emp VALUES('vishwanath');
 INSERT into @emp VALUES('chetan');
 INSERT into @emp VALUES(NULL);
 INSERT into @emp VALUES(NULL);

 SELECT * FROM @emp
 where len(fname)>=1 and fname<>'';

 Gives..

     fname
--------------------------------------------------
    vishwanath
    chetan

edited Jul 23 '12 at 08:43

answered Jul 23 '12 at 07:05

Vishwanath Dalvi

35,388
41
123
155

1

`INSERT INTO @emp VALUES('')` - what about this one then? It is not NULL, just empty. – cairnz Jul 23 '12 at 07:40

score 0 · Answer 4 · answered Jul 23 '12 at 07:11

0

Both are correct if the values in the column are either greater than 0 or null. You can refer to this post if you want to know about the weird behavior of nulls in SQL Server.

answered Jul 23 '12 at 07:11

Egalitarian

2,168
7
24
33

score 0 · Answer 5 · answered Jul 23 '12 at 09:01

when you are checking whether a column is null or not it is better to use col IS NULL

when you use ISNULL(TABLE.COLUMN,0) > 0) function , null values have to be converted to zero fist then should take all values greater than zero

this function is useful in another occasion. lets say if I want to return all the null values as well as the negative values.

so the query would be

select * from table where col is null or col<0

this can be re-written as

select * from table isnull(col,-1)<0

score 0 · Answer 6 · answered Jul 23 '12 at 09:33

Both are correct in there cause, however ISNULL can be helpfull when you want to use a constant value instead of NULL in that column while calculating SUM, average, etc.
For example you can check :http://www.w3schools.com/sql/sql_isnull.asp

Due to this feature I personally use ISNULL/COALESCE for calculation purposes.

Handling NULLs in SQL query

6 Answers6