ISNull : This function requires 2 arguments, check if first argument is NULL then replace NULL value with second argument , exactly works as :
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.
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
Insert Into #Names
Select * From #Names
--Now show FirstName + Last Name, but if First and Last names both are same, show only FirstName
---With Case When
Case When FirstName = LastName Then FirstName Else FirstName + ' ' + LastName End Name
---With ISNULL & NULLIF
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.