- 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
SQL Server ISNULL() With Multi Column Names
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
Sanjit Kumar SinghPosted May 10, 2019, 10:50 AM
Awesome Article.
Laxmidhar SahooPosted Apr 26, 2019, 10:42 AM
Thanks a good article
Prasad KrishnaraoPosted Nov 27, 2018, 10:21 AM
Very good explanation
Krishna RanaPosted Nov 26, 2018, 11:31 PM
Good explanation,