Tuesday, June 22, 2010

ISNULL, NULLIF and COALESCE Functions

ISNull : This function requires 2 arguments, check if first argument is NULL then replace NULL value with second argument , exactly works as :

Select
Case When FirstArgument Is Null Then SecondArgument Else FirstArgument End As WithCaseWhen


Simply this type of statement can be handle using ISNULL function like

Select ISNULL (FirstArgument, SecondArgumanet)

COALESCE : When we have to check multiple arguments suppose:

A contact table contains 3 types of contact numbers ( HomePhone, WorkPhone, CellPhone), and all these three columns are with allow Null value.
and now want to fetch any one Non Null phone number , we are sure that this requirement can be done with case when statement, but no need to write complex
case When statement , sql server provide a function to check multiple argument and to select first Non Null value.


So COALESCE function returns the first non-NULL expression among its arguments.

e.g.
Select COALESCE ( HomePhone, WorkPhone, CellPhone)


NULLIF : ISNULL and COALESCE functions are to replace Null with Non Null value , but sometimes we need to replace Non Null value with Null value, like

Select Case When FirstAgument = SecondArgument Then Null Else FirstAgument End


The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned.

Try this sql:

Create Table #Names
(
FirstName Varchar(20),
LastName Varchar(10)
)

Insert Into #Names
Select 'Randhir','Randhir'
Union
Select 'Satish','Verma'
Union
Select 'Sumit','Gure'

Select * From #Names

--Now show FirstName + Last Name, but if First and Last names both are same, show only FirstName

---With Case When
Select
Case When FirstName = LastName Then FirstName Else FirstName + ' ' + LastName End Name
From #Names

---With ISNULL & NULLIF
Select
ISNULL(NULLIF(FirstName,LastName),FirstName) Name
From #Names




Here NULLIF(FirstName,LastName) expression will show NULL in case of fist record because both first & Last Name are same.
and IsNull replace the Null value with FirstName.

No comments: