Different Ways To Handle NULL In SQL Server

Introduction

In this tutorial, I am going to explain various methods to handle NULLs in SQL Server. This detailed article will cover the following topics as follows:

1. Introduction
2. What exactly is NULL?
3. Different Ways to handle NULLs
4. Difference between ISNULL and COALESCE Function
5. Conclusion

What Exactly Is NULL?

A NULL is a term with a value other than 0 or an empty string, i.e. an unknown value that data is missing.

As per Wikipedia, "Null or NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. A null should not be confused with a value of 0. A null value indicates a lack of a value, which is not the same thing as a value of zero. SQL null is a state, not a value."

First, let's create a database with a table containing some dummy data. Here, I am providing you with the database along with the table containing the records, on which I am showing you the various examples. Let's see.

CREATE TABLE HandleISNULL(
	EmployeeID INT IDENTITY (31100,11),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	EmployeeFullName VARCHAR (100) NOT NULL,
	DefaultTask VARCHAR(100),
	Salary BIGINT,
	PrimaryContactNo BIGINT,
	SecondaryContactNo BIGINT,
	Telephone INT
	PRIMARY KEY (EmployeeID)
)

Now, insert some dummy data in it.

INSERT INTO HandleISNULL VALUES
(DEFAULT, 'Prabhas', 'Development', 2100000, 789654123, 741258963, NULL),
(DEFAULT, 'John Sinha', NULL, NULL, 123654789, NULL, NULL),
(DEFAULT, 'Meenu Tiwari', 'HR', 1800000, 456987132, 963258741, NULL),
(DEFAULT, 'Neeraj__Chopra', NULL, NULL, 951478632, NULL, NULL),
(DEFAULT, 'Neeraj Chopra', 'Marketing', 900000, 485936217, NULL, NULL),
(DEFAULT, 'Onkar Sharma', 'Admin', 5200000,369147852, 326159874, NULL),
(DEFAULT, 'Bajrang Punia', 'HR', 1700000, 98653247, 784512369, NULL),
(DEFAULT, 'Satish', 'Development', 2300000, NULL, NULL, NULL),
(DEFAULT, 'Parneeti Dhoppra', 'Support', 700000, 123654789, 48963157, NULL),
(DEFAULT, 'Ananya Pandey', NULL, 300000, 547896321, NULL,NULL),
(DEFAULT, 'Shraddha Kapoor', 'Account', 1500000, NULL, NULL, 265819),
(DEFAULT, 'Akshay Sharma', NULL, NULL, 648521789, NULL,NULL)

Let's check our following table by using the following query.

SELECT * FROM OnkarSharma..HandleISNULL

Note
Here, we can see that symbols “=” and “!=” do not work with “NULL” values as follows.

-- Wrong Query
SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask = NULL

SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask != NULL

-- Right Query
SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask IS NULL

SELECT * FROM OnkarSharma..HandleISNULL
WHERE DefaultTask IS NOT NULL

ISNULL() Function

The ISNULL() returns a specified replacement value if the expression is NULL. It replaces NULL with the specified replacement value if the expression is NULL.

Key Points

• ISNULL() takes only two parameters.
• ISNULL() is Microsoft SQL Server-specific.
• The ISNULL() return value is always considered NON-NULLable.
• The first parameter's data type determines the data type of the value returned by ISNULL().

Syntax

ISNULL(Expression, ReplacementValue);

Examples

The examples in this section demonstrate the functionality of the ISNULL() function. Let's see.

Simple Example

SELECT ISNULL (NULL, 108) NULL_REPLACEMENT;
SELECT ISNULL (NULL,'SQLServer') NULL_REPLACEMENT;
SELECT ISNULL ('OnkarSharma','SQLServer') NULL_REPLACEMENT;
SELECT ISNULL (NULL,GETDATE()) NULL_REPLACEMENT;

Complex Example

Replace the NULL with custom values/messages.

The following example returns the list of all employees who did not provide an alternate phone/contact number.

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
ISNULL(DefaultTask, 'Trainee') AS DefaultTask, PrimaryContactNo,
ISNULL(CAST(SecondaryContactNo AS VARCHAR), 'NOT Available') AS 'Secondary Contact No'
FROM OnkarSharma..HandleISNULL
WHERE SecondaryContactNo IS NULL

COALESCE() Function

The COALESCE() function returns the first NON-NULL value from the list provided. As per Wikipedia, "The COALESCE function accepts a list of parameters, returning the first non-null value from the list".

Key Points

• COALESCE is an ANSI standard function.
• COALESCE() function can contain multiple expressions.
• Expressions in COALESCE() must be of the same data type.
• The Coalesce function is a syntactic shorthand for the SQL Case expression.
• It always evaluates for an integer first, an integer followed by character expression yields an integer as an output.
• Returns the data type of expression/value with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.

Syntax

COALESCE (expression1, expression2,... expressionN);

Examples

The examples in this section demonstrate the functionality of the COALESCE() Function. Let's see.

Simple Example

SELECT COALESCE (NULL,'A','B') NULL_REPLACEMENT
SELECT COALESCE (NULL,100,120,130,140) NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,521,NULL,NULL) NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,'','C-sharpcorner') NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,NULL,'Onkar Sharma') NULL_REPLACEMENT
SELECT COALESCE (NULL,NULL,NULL,1,'Onkar Sharma MVP') NULL_REPLACEMENT

Note
Note that, NULL and blank space is not the same. In the above example, COALESCE returns a blank space showing us that a blank space (empty string) is the first non-NULL value in the list.

Complex Example

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
COALESCE(CAST(PrimaryContactNo AS VARCHAR), CAST(SecondaryContactNo AS VARCHAR), CAST(Telephone AS VARCHAR), 'NOT Available') AS 'Available Contact Number'
FROM OnkarSharma..HandleISNULL

CASE Statement

The Case Statement in SQL Server is used to evaluate a list of conditions and return one of the possible result expressions. The CASE Statements has two formats, viz, Simple CASE expression and Searched CASE expression.

Key Points

• CASE can be used in any statement or clause that allows a valid expression.
• SQL Server allows only 10 levels of nesting in CASE expressions.

Syntax

Simple CASE expression

CASE [input_expression]
WHEN [expression] THEN [result_expression] ELSE [else_expression]
END

Searched CASE expression

CASE
WHEN [expression] THEN [result_expression]
ELSE [else_expression]
END

Examples

The examples in this section demonstrate the functionality of the CASE statement. Let's see.

SELECT
EmployerID,
EmployeeID,
EmployeeFullName,
CASE WHEN DefaultTask IS NULL THEN '*Trainee' ELSE DefaultTask END AS DefaultTask,
CASE WHEN Salary IS NULL THEN 360000 ELSE Salary END AS Salary
FROM OnkarSharma..HandleISNULL

Difference Between ISNULL and COALESCE Functions

Now, let's see the quick difference between ISNULL() and COALESCE() Functions

S.No.	ISNULL()	COALESCE()
1	ISNULL() is Microsoft SQL Server-specific.	COALESCE is an ANSI standard function.
2	ISNULL() takes only two parameters.	COALESCE() Function can contain multiple argunments/expressions.
3	The COALESCE expression is a syntactic shortcut for the CASE expression.	Expressions in COALESCE() must be of the same data type.
4	The first argument's data type determines the data type of the value returned by ISNULL.	Returns the data type of expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.
5	The ISNULL return value is always considered NON-NULLable.	COALESCE with non-null parameters is considered to be NULL.

You may also visit my article, Difference Between Delete, Truncate, And Drop Statements In SQL Server.

Conclusion

In this article, we have discussed various methods to handle NULLs in SQL Server.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.