Handle NULL in SQL Server

• Download source - 1.1 KB

What is NULL?

In databases, a common problem is, how to represent missing values. In SQL Server, this is solved with NULL. It is used to signify missing or unknown values. The keyword NULL is used to indicate these values. When referring to the NULL value, most of the time, we called "the NULL value". NULL really isn’t a specific value, a non-existent value, not a zero value or not an empty string value. Two NULL values are not equal.

NULL is as a setting that indicates if a data value does not exist. The database engine uses a special bitmap to track which columns in a row are NULL and which are not. The bitmap contains a bit for each column, with the bit set to 1 if the column is NULL.

For example, the following SELECT statement returns data from the BookDetails table.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails

As the following results show:

In the above result, some columns contain missing or unknown values. SQL reserves the NULL keyword to denote an unknown or missing value. In SQL NULLs are treated differently than values. Special consideration is required when comparing null value or using them in expressions.

NULL Value in Comparisons

Developers, particularly beginning T-SQL developers, who don’t understand how NULL works will sometimes use comparison operators to compare an expression to a NULL value.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

The assumption is, NULL is a valid value and can be compared to other NULL values. So the above statement should return each row whose ReleaseDate value is NULL. So the BookId 1, 3 will return as a result of the assumed logic. But the database engine cannot compare nothing to nothing (NULL to NULL). It will return and empty result set.

We need to return the non-NULL data:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

Above statement trying to compare real non-NULL values to NULL. It seems to be a credible logic. So the BookId 2, 4 and 5 will return as a result of the assumed logic. But we cannot compare real values to nothing. As a result, the statement still returns no data, even though we know for certain the data exists.

Proper way to do the NULL comparison

SQL covers this dilemma though. We can use the IS NULL and IS NOT NULL comparisons to test for NULL values.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NULL

The above SELECT statement uses the IS NULL operator to return rows whose ReleaseDate is NULL.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NOT NULL

The above SELECT statement uses the IS NOT NULL operator to return rows whose ReleaseDate is not NULL.

Set ANSI_NULL for NULL Value Comparisons

SQL Server supports a feature called ANSI_NULLS, which determines how the database engine handles NULL comparisons. The option ANSI_NULLS controls whether we can use an equality operator with NULL to mean the same thing as IS NULL. The ANSI SQL standard specifies NULL is never equal to anything.

By default, SQL Server installs with the ANSI_NULLS option turned on, which give the above described result.

Check current ANSI_NULLS setting:

IF ((32 & @@OPTIONS) = 32)
  PRINT 'The ANSI_NULLS option turned on.'
ELSE
  PRINT 'The ANSI_NULLS option turned off.'

If there is no change with ANSI_NULLS in database engine, it will return "The ANSI_NULLS option turned on."

We can turn off the ANSI_NULLS option. A SET command changes behaviour only for the connection where we issue the command. If we issue a SET command in a stored procedure, the command usually controls that procedure's behaviour.

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

ISNULL & IS NULL

Another confusion with ISNULL and IS NULL. ISNULL implies a meaning similar to the IS NULL operator. Let’s understand with the examples.

Return book details whose author value is NULL.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE Author IS NULL

If we don't understand ISNULL, we might try to retrieve the same result by using ISNULL in WHERE clause:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author)

Fortunately, we might end up with error message.

The purpose of ISNULL function is to replace an expression's value with a real value. If we wanted to return the above example right, we would need to define our Boolean condition correctly.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author, 'nonexistent') IN ('nonexistent')

More often, we want to use the ISNULL function is within our SELECT statement to replace NULL values with a expression's value.

SELECT BookId, Title, ReleaseDate, Price, ISNULL(Author, 'nonexistent') AS Author
FROM BookDetails

The column Author now having a value for all rows. All the NULL changed as nonexistent.

COALESCE

Before going further, ISNULL - Implemented in SQL Server is specific to that brand of T-SQL, COALESEC is standard ANSI. Both functions let us replace NULL with an expression's value, but there are differences between the two functions. The COALESEC function allowed to more than two expression.

For example, the following SELECT statement returns data from the StudentDetails table.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails

Let's apply COALESE function for the above SELECT statement.

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result
FROM StudentDetails

The COALESCE function evaluate the expression in the order they are specified and returns the first non-NULL value. COALESE_Result column show the COALESE function result.

Actually COALESCE function is a shorthand for CASE expression, but CASE expression construction is complex. The advantage of CASE expression is, it show the logic very clear.

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result,
CASE
	WHEN Semester_I IS NOT NULL THEN Semester_I
	WHEN Semester_II IS NOT NULL THEN Semester_II
	WHEN Semester_III IS NOT NULL THEN Semester_III
	ELSE 0
END CASE_Result
FROM StudentDetails

COALESCE Vs. ISNULL

ISNULL function is limited. Specify only two expressions.

SELECT StudentId, StudentName, Department, 
Semester_I, ISNULL(Semester_II, 0) AS ISNULL_Result, Semester_III
FROM StudentDetails

In this case, we defined that, if Semester_II is NULL, then use 0.

COALESCE handles data types differently. To understand this, let’s start with an example:

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails
WHERE StudentId = 5

StudentId, Semester_I, Semester_II and Semester_III columns are defined as Int, StudentName and Department columns are defined as Varchar. The above statement returns the following result.

Now let's include COALESCE function.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
COALESCE(Department, StudentName, Semester_I) AS COALESCE_Result
FROM StudentDetails
WHERE StudentId = 5

This time, we end up with an error message.

COALESCE uses the type with the highest precedence, based on the submitted values. Semester_I column takes an Int and Int take precedence over Varchar. So database engine tries to convert EEE to an Int value. The database engine does not care about the order.

Now let's include INSULL function.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Department, Semester_I) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

In the above SELECT statement ISNULL function Department sets the standard, it is a Varchar data type. So database engine has no problem.

Now, let's change the order of ISNULL function.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Semester_I, Department) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

This time also, we end up with an error message. Because, Semester_I sets the standard, it is an Int data type. So database engine cannot convert the 'EEE' to Int data type.

Any Value + NULL = Any Value?

When we try to do the calculation, like add a value to nothing or subtract from nothing or multiply by nothing or divide by nothing or do anything by nothing, we end up with nothing, or NULL. Take a look at this SELECT statement:

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(Semester_I + Semester_II + Semester_III) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

When we try to calculation (Used Addition here) by NULL, we get the NULL. We can make it reasonable by defining a default value to the NULL.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(ISNULL(Semester_I, 0) + ISNULL(Semester_II,0) + ISNULL(Semester_III,0)) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

Here, we used ISNULL function with default value as 0 for each parameters inside the add function. So now Semester_I returns 0, so TotalResult showing 90.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL((Semester_I + Semester_II + Semester_III),0) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

Here we used ISNULL function with default value as 0 for the result of the Addition.

So Semester_I is null, then Addition result return NULL. Outside ISNULL function returns the default value 0.

Summary

I hope, this explanation has given some information to handle NULL in SQL server.