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:

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:

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:

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:

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