SQL Server ISNULL() With Multi Column Names

Many 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.

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)

The 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

The 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 moving 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 purposes. If a passport number is null, then we need to extract the license number. If the license number is null then pan, if a pan is null then account number. If all are null then we need to flag a message as ‘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