Different ways to handle NULLs in SQL Server

Many times when we query the database, we get NULLs in the result set and we want to replace those NULLs with some other values for many different reasons. Let us see how we can replace those NULLs using various methods in SQL Server.

As shown below, we have a table called information which stores the data of employee name, department and salary. 3 records have NULLs as their department.

There are 3 ways we can replace those NULLs.

1) Using ISNULL() function - TSQL specific function

2) Using COALESCE() function - ANSI SQL standard function

3) Using a CASE statement

1) ISNULL()

ISNULL() is a TSQL function that allows us to replace NULL with a value of our choice. The syntax is ISNULL(expression, replacement) where the expression is the column we want to check and the replacement is the replacement value that we want to use in case if there is any NULL in the column specified.

Let us understand this with an example. Let us say in our information table, we want to replace NULLs in the department column with 'NO DEPARTMENT'. Therefore, our syntax will be ISNULL(department, 'NO DEPARTMENT') meaning if there is any record with NULL then replace that record with 'NO DEPARTMENT'.

We can see in the picture above that all those 3 records with NULL got replaced with 'NO DEPARTMENT' value.

2) COALESCE()

COALESCE() function returns the first non-null value from the defined argument list. The syntax for COALESCE() is COALESCE(argument1, argument2, ... , argumentN). So, when executed, it first checks whether the first argument is NON NULL or not and if yes, it returns that value and if not then it checks the second argument and so on until it finds the first NON NULL value in the list.

Let us see that in action with an example. Let us say in our information table, we want to replace NULLs in the department column with 'NO DEPARTMENT'. Therefore, our syntax will be COALESCE(department, 'NO DEPARTMENT') meaning it will first check the value of the department column and if that is NON NULL, it will return that value otherwise, it will check the second argument which in our case is 'NO DEPARTMENT' and since it is a NON NULL value, it will be returned.

3) CASE statement

CASE statement can be used to define that if a record has NULL value then replace it with a value of our choice. Let us see an example of it.

Here, we are defining a CASE statement saying that for all the values of the department column which are NULL, replace it with the 'NO DEPARTMENT' value.

Hope this helps. Thank you!