Working with NULLS in SQL Server – PART 3

You must have seen in my previous blog that how Nulls can be handled using COALESCE () function. If you want to refer that again, then browse this link:

In this blog post, I will deal with how to handle the Nulls using ISNULL () and NULLIF () functions.

So I would like to discuss each of them separately:

ISNULL () Function:

  • ISNULL () is the most common handling function which is quite different from the IS NULL search condition.
  • It accepts two parameters: source_expression and replacement­_value.
  • If the Source is not equal to the NULL, then the ISNULL () function will passes the value as if untouched. But if the Source is NULL, then the second parameter (replacement_value) is being substituted in place of NULL.
  • It can be represented as:

ISNULL(source_expression, replacement_value)

  • This will become more clear when you see this example:

1_Working_with_NULLS_in_SQL_Server_PART_3

Since it contains many NULL values, so it’s better to replace each of them using ISNULL() function:

SELECT S_Id, Name, Enrl_No, 
ISNULL(Science_Stream,'NO') AS Science_Stream, 
ISNULL(Commerce_Stream,'NO') AS Commerce_Stream,
ISNULL(Art_Stream,'NO') AS Art_Stream
FROM Student;

You can view the results as:

2_Working_with_NULLS_in_SQL_Server_PART_3

So this was all about ISNULL() function.

NULLIF () Function:

  • NULLIF () is an ANSI standard SQL function.
  • It is a function which is being used to return NULL values, if the two specified expressions are equivalent.
  • They are generally being used to replace surrogate nulls (‘N/A’, ‘blank’, or ‘-‘) in most of the cases where it’s better to replace them with NULLs. So it looks clean.
  • NULLIF () function can be represented as:

NULLIF (Expression1, Expression2)

  • This will become more clear, if you go through this example carefully-

I am updating one cell of Commerce_Stream to ‘ ‘ (empty), so it would become easy for you to understand:

USE TEST
UPDATE Student
SET Commerce_Stream=''
WHERE Name='Piyush Bajaj';

Now you can see the table, how it looks:

3_Working_with_NULLS_in_SQL_Server_PART_3

So Next, I will display five columns, in which three will be S_Id, Name, and Enrl_No. While the other two are the main columns which include Commerce_Stream_Blank with a surrogate null (Blank) and the other is Commerce_Stream_NULL without a surrogate null, i.e. we have used here null instead using NULLIF () function:

SELECT S_Id, Name, Enrl_No, 
CASE Commerce_Stream
WHEN '' THEN 'Blank'
ELSE Commerce_Stream
END AS Commerce_Stream_Blank,
NULLIF(Commerce_Stream, '') AS Commerce_Stream_NULL 
FROM Student;

Therefore, we can see the result in the result set:

4_Working_with_NULLS_in_SQL_Server_PART_3

Therefore, you can see the difference in the two columns one with a Blank while other with a NULL.

Well this was all about Working with NULLS in SQL Server and so we come to an end of these sequel.

Hope you enjoyed reading it and learned. 🙂

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *