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.
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.
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:
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:
Image3-IsNull-Multiple-Columns
In the above select statement, we have used IsNull function 5 times to get the desired output.
Syntax of Coalesce:
In other words, we can say that coalesce behaves as per the following syntax as well:
Image4-Coalesce
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 ENDSo 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.
1 comment:
COALESCE is ANSI standard function which is internally translated to a CASE expression. Coalesce allows multiple items to be compared in one statement. COALESCE () most often appears within a very specific content, such as in a query or view or stored procedure. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression.
SQL - COALESCE ()
Post a Comment