Working with NULLS in SQL Server – PART 2

You would have seen in my previous blog that how Nulls can be tested. 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 some functions.

Handling NULLS:

At the time, when we supply the data to the end users, or to any applications so the Null should either be removed or should be replaced with a valid value so that the data may be understood, and the expression wont fail.

Since Nulls require special handling when used within expressions, so SQL includes three functions, COALESCE () and ISNULL () for converting Nulls to usable values, and NULLIF () for creating a Null if the condition is being met. We will discuss each one of them briefly:

COALESCE () function:

  • COALESCE () function accepts a list of expressions or columns and returns the first non-null value.
  • It can be represented as: COALESCE (expression1, expression2 …).
  • It generally means to unite towards a common end, to grow together, and to bring opposite sides together for a common good.
  • This function brings together multiple, differing values of unknown usefulness, and from them emerges a single valid value.
  • It is not used as often as it should be, might because of not well-known.
  • For example;
    • The following code demonstrates the COALESCE () function returning the first non-null value-
SELECT COALESCE(NULL, 100+NULL, 7, 'A');

The results can be displayed as follows:

1_Working_with_NULLS_in_SQL_Server_PART_2

  • It will become more clear if you carefully refer to this example, in which the Students belong to which stream is being displayed:

1. First , we have to create a table-

USE TEST
CREATE TABLE Student
(
S_Id int not null,
Name varchar(50) not null,
Enrl_No nvarchar(50) not null,
Science_Stream varchar(10) null,
Commerce_Stream varchar(10) null,
Art_Stream varchar(10) null

2. Then, insert some records-

INSERT INTO Student (S_Id, Name, Enrl_No, Science_Stream, Commerce_Stream, Art_Stream)
SELECT 1, 'Piyush Bajaj', 'AC2389', 'Yes' ,NULL, NULL
UNION
SELECT 2, 'Prashant Tiwari', 'ADY654', NULL, 'Yes', NULL
UNION
SELECT 3, 'Ankit Ritolia', 'AS2378', NULL, NULL, 'Yes'

3. Let see the table now-

2_Working_with_NULLS_in_SQL_Server_PART_2

As you can see here, there are many NULL present inside the table. So what we have to do is to handle the NULL values, i.e., to replace them with some strings. We can do this by using a COALESCE() function.

4. So by using COALESCE() function we will replace the NULLs with string ‘NO’, which may be understood by the end-users:

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

The table can be viewed in a result set as follows:

3_Working_with_NULLS_in_SQL_Server_PART_2

Well this was all about COALESCE() function for this post, and in the next blog post I would like to deal with two more functions ISNULL() and NULLIF().

So keep in touch!

 

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

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 →

2 Comments on “Working with NULLS in SQL Server – PART 2”

  1. Piyush,

    Would be great if you could post some performance implications in using the different methods.

  2. Hey Sachin,

    I think so Amit Bansal would be the right person to talk about “performance implications…….” 😉

Leave a Reply

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