In previous post(part 1) we learned about
tristate logic for NULL ,Not Exists and Null propagation.In this post I will be
explaining about ISNULL ,COALESCE, IS NULL, NULL relation with aggregate
functions etc.
First of all lets
create a temporary table to explain
CREATE
TABLE #temp
(
name
varchar(20),
age
int
)
INSERT INTO #temp VALUES('satya',26)
INSERT INTO #temp VALUES('biswa',NULL)
INSERT INTO #temp VALUES('panu',15)
INSERT INTO #temp VALUES('rahul',NULL)
INSERT INTO #temp VALUES('devi',35)
In
last post I said any expression involving NULL becomes NULL. Like NULL
+10,NULL/0 etc. But aggregate functions like SUM ,AVG just ignore NULL while computing.
eg.
SELECT AVG(age) FROM #temp - >25
SELECT SUM(age) FROM #temp - >76
IS NULL/IS NOT NULL Predicate
If requirement is
select record from table #temp for which age is equal to NULL ..
First temptation
is to use below query
SELECT * FROM #temp where
age = NULL
But as I
explained in previous post about NULL propagation this wont return any
records.The IS NULL operator is to be used in this kind of senario.
SELECT * FROM #temp where age IS NULL
This will fetch
all record where age is NULL .
o/p
biswa
NULL
rahul
NULL
Now I want
records for which age is not NULL.
SELECT * FROM #temp where age IS NOT NULL
OR
SELECT * FROM #temp where NOT age IS NULL
This will fetch
all record where age is not NULL .
O/P
satya
26
panu
15
devi
35
ISNULL/COALESCE
Function:
ISNULL function
takes 2 arguments and returns the first non-null value.
Syntax:
ISNULL(arg1,arg2) (takes 2 arguments)
Here if arg1 is
not null it will return arg1 ,
if arg1 is
null, arg2 is not null it will return arg2
else if both are
null it will return NULL.
The dataype of
return value is same as datatype of arg1.
So if I want
records with NULL values I can write as
SELECT * FROM #temp where ISNULL(age,0)=0
To get records
without NULL column
SELECT * FROM #temp where ISNULL(age,0)!=0
COALESCE function
work similar to ISNULL but it can take anynumber of arguments .
Syntax:
COALESCE(arg1,arg2,arg3,arg4)
Here return value
will be the first non-null argument.
Eg.: SELECT * from #temp where COALESCE(age,NULL,0)=0
As it can take
any number of parameters it has more advantage compared to ISNULL.Also COALESCE
is ANSI standard .
Another very
important fact is ISNULL returns data with a datatype same as datatype of first
parameter.This causes problem in some situations.
Eg. Add one more
row in #temp
INSERT INTO
#temp VALUES(NULL,35)
SELECT
ISNULL(name,'satyajit mohanty is the author') FROM #temp where ISNULL(name,'')=''
Will return
‘satyajit mohanty
is’
Remember ISNULL
will return data with same datatype as
argument 1. Here it is varchar(20) .So o/p is a varchar with length
20.This would lead to wrong results when you tru to us this data or try to
insert it into some other table.
On the other hand
COALESCE will return data with a datatype which is same as datatype of argument
in accordance with highest datatype presedence.
Just replace
ISNULL with COALESCE and check the output which will be correct one.
Also try this
COALESCE(name,'satyajit mohanty is the author',0)
Remember datatype
would be datatype of argument in accordance with highest datatype precedence
before running above expression in a query.
So try to avoid
ISNULL esspecially when you are using the output from ISNULL in further
manipulation/storage.
Strange:
When try to run
below queries
CREATE TABLE #temp1
(
class
int NOT NULL,
age
int NULL,
mark
AS ISNULL (class,age) Primary Key
)
CREATE TABLE #temp1
(
class
int NOT NULL,
age
int NULL,
mark
AS COALESCE(class,age) Primary Key
)
Here the columns
with ISNULL/COALESCE are called as computed columns.It is strange that
first query will run sucessfully where as query 2 wont . I ran this on sql
server 2008 . Don't know the reason yet.If you find/know the reason please post a
reply.
So in this case
ISNULL is unavoidable.
In next article
we will see on NULLIF as this article
already getting sufficiently large :) .
No comments:
Post a Comment