SQL ISNULL, IFNULL and NVL function with example

ISNULL in Sql Server is uded to check and return alternate value if value is NULL, while to only check the NULL value, we can us IS NULL with space, in MySQL and Sqlite, for same functionality, we can be use IFNULL and in Oracle we can use NVL.

Suppose we want to return 0 if column value is null then we can use

select ISNULL(columnName, 0) from table_name

Column type and alternate return type should be same, say the column type is INT then alternate value must also be INT, it cannot be string. Let say we have following query

Select  emp_id, first_name + ' ' + last_name as [full_name]
   salary,  bonus,  salary + bonus [total_salary]
From employee

Suppose last is null and discount is null then what would be the value for fullname and totalsalary column, any guess, they will be null, so to fix the issue we can use ISNULL and our query will be

Select  emp_id, first_name + ' ' +  ISNULL(last_name) as [full_name]
   salary,  bonus,  salary + ISNULL(bonus, 0) [total_salary]
From employee

Take another example where you need to calculate percentage and divisor may be NULL then the result will be null Select col1 * col2 / col3 as [result] From table In this case if col3 is NULL then result will be NULL while it should be col1 * cul2, so fix this issue we can check if col3 is NULL then it must be divided by 1, and it can be achieved by using ISNULL function in SQL Server

Select  col1 * col2 / ISNULL(col3, 1) as [result] From table

IS MySQL and Sqlite we can use IFNULL, like this

Select  emp_id, first_name + ' ' + IFNULL(last_name) as [full_name]
   salary,  bonus,  salary + IFNULL(bonus, 0) [total_salary]
Srom employee

And finally in Oracle we can use NVL, like this

Select  emp_id, first_name + ' ' + NVL(last_name) as [full_name]
   salary,  bonus,  salary + NVL(bonus, 0) [total_salary]
From employee

Let’s see how we can only check null value in a column, say we want to see all those employee where last name is NULL and bonus is NULL

select * from employee
where last_name IS NULL
   and bonus IS NULL

It will return only those records where last name is NULL and bonus is NULL.

Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • sql server
  • mysql
  • sqlite
By Hamden On 31 Aug, 13  Viewed: 2,091

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 336

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 456

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,057

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 304

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 4,508