SQL Server ISNULL() With Multi Column Names

Many a times we come across null values within tables in SQL Server. Null values are the values with no data; that means the data is missing or unknown. In this article, initially we will understand the SQL Server IsNull function, then we will move towards extending the IsNull functionality using Coalesce function.

Let us see the following example.

create table Identification
(empid Integer,
ename varchar(30) NOT NULL,
passport_number char(15) ,
license_number char(15) ,
pan char(15) ,
credit_card_number char(15) ,
account_number char(15) 
)
insert into identification values(1,'John',null,null,'PN-78654','CC-12345','AN-3456')
insert into identification values(2,'Martin','PS-566774',null,null,null,null)
insert into identification values(3,'Smith',null,null,null,null,null)
insert into identification values(4,'Roger',null,null,null,null,'AN-9876')
insert into identification values(5,'King',null,null,null,'CC-8787','AN-9878')
go
select * from identification
go

Image1-Identification-Table

In the above table, every employee has an identity proof which is either a passport number, license number, pan, credit card or account number.

SQL Server ISNULL() Function Example

Syntax:

IsNull(Parameter1, Value if null)

IsNull function returns the first parameter if it’s not null. If the first parameter is null, then it returns the second parameter.

Suppose we want to see if an employee has a passport or not, here the IsNull function can help us.

See the following example of using SQL Server ISNULL in a Select Statement:

select empid, ename, IsNull(Passport_Number, 'Not Found') 
as 'Passport Status' from identification

Image2-IsNull-With-Single-Column

Limitation of IsNull() function:

IsNull function can check only if one value is null. It cannot check null for multiple values. That means it is not capable of handling the functionality of checking if the first parameter is null and then move on to check the next parameter for null.

Now assume that for report generation, we want to get the passport number or license number or pan number etc. for reference purpose. If passport number is null, then we need to extract the license number. If license number is null then pan, if pan is null then account number. If all are null then we need to flag a message ‘Invalid’.

The problem is that IsNull function here needs to be used in a nesting manner.

Let us see the ISNULL being used in a select statement in a nested fashion:

select empid, ename, 
IsNull(Passport_Number,IsNull(License_Number, 
IsNull(PAN, IsNull(Credit_Card_Number, IsNull(Account_Number,'Invalid'))))) 
as "Status"
from identification

Image3-IsNull-Multiple-Columns

In the above select statement, we have used IsNull function 5 times to get the desired output.

ISNULL vs Coalesce Function:

There is an alternative way using another SQL Server function known as Coalesce.

Syntax of Coalesce:

COALESCE( parameter1, parameter2, parameter3,……. parameter_n , default_parameter)

Coalesce can take multiple parameters. It returns the first not null parameter. If all the parameters are null, then it will return the default parameter.

In other words, we can say that coalesce behaves as per the following syntax as well:

CASE
   WHEN (parameter1 IS NOT NULL) THEN expression1
   WHEN (parameter2  IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

So instead of nesting IsNull function multiple times, we can use a single coalesce function and get the same output as shown here:

select empid, ename, 
Coalesce(Passport_Number,License_Number,PAN,Credit_Card_Number,Account_Number,'Invalid')
as "Using Coalesce" from identification

Image4-Coalesce

Conclusion:

We have seen how the SQL Server IsNull function is suitable for checking one null value and how usage of coalesce function can eliminate the need of using IsNull function redundantly.