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 ....
-
2Possible duplicate: stackoverflow.com/questions/3118213/isnull-vs-is-nullMarek Dzikiewicz– Marek Dzikiewicz2012-07-23 06:59:47 +00:00Commented Jul 23, 2012 at 6:59
Add a comment
|
6 Answers
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!!!
Comments
Second one is right if you want to check for null value in SQL case..
Comments
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.
Comments
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
answered Jul 23, 2012 at 7:05
Vishwanath Dalvi
36.9k41 gold badges128 silver badges156 bronze badges
1 Comment
cairnz
INSERT INTO @emp VALUES('') - what about this one then? It is not NULL, just empty.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
Comments
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.
