SQL 4 begginers - NULL

In most online articles about databases you can easily find a lot of articles explaining cool, complex and new features. But what’s about some basics? I decided to release some articles about the basics. The articles will mostly cover topics usable in most relational databases, but the examples and the focus will be on MS SQL Server database engine.

When talking about values in database world there are three possible states. The three states are:

  • there is a value (e.g. "John Doe"),
  • there is not a value - empty or zero (e.g. "") and finally
  • the value is NULL (unknown).

Let's find out more about the NULL value.

In the example of the toilet paper roll the illustration could be:

NULL value means that the value is unknown. It is not empty or zero.

If you try to compare a NULL value with another (even empty) value the result will be false.

Example:

DECLARE @EmptyValue VARCHAR(256) = ''
DECLARE @NULLValue VARCHAR(256) = NULL

SELECT CASE WHEN @EmptyValue = @NULLValue THEN 'The values are equals' ELSE 'The values are not equal' END AS Result

Also, if you try to do some operations (e.g. concatenate strings or sum values) the result of such operations will always be NULL if only a single value is NULL.

Example:

DECLARE @v1 INT = 9
DECLARE @v2 INT = NULL
DECLARE @v3 INT = 0

SELECT @v1 + @v2 + @v3 AS Result

The reason for that is that something compared or added to unknown is still unknown.

What if we compare two NULL values?

Example:

DECLARE @NullValue_1 VARCHAR(256) = NULL
DECLARE @NullValue_2 VARCHAR(256) = NULL

SELECT CASE WHEN @NullValue_1 = @NullValue_2 THEN 'The values are equals' ELSE 'The values are not equal' END AS Result

Ok, that's wired :) So if something is unknown it is impossible to compare it with another unknown. The result is.. unknown!

What could be the solution for this problem and how to handle NULL values?

If we want just to check if something is NULL we can use the IS (NOT) NULL statement.

Example:

DECLARE @NullValue VARCHAR(256) = NULL

SELECT CASE WHEN @NullValue IS NULL THEN 'The value is null' ELSE 'The valueis not null' END AS Result

Using ISNULL function, we can replace a NULL value with something else if we need it for some operations (concatenation, compare, etc.). Let's solve our examples from the beginning of this article.

Example:

DECLARE @EmptyValue VARCHAR(256) = ''
DECLARE @NULLValue VARCHAR(256) = NULL

SELECT CASE WHEN @EmptyValue = ISNULL(@NULLValue, '') THEN 'The values are equals' ELSE 'The values are not equal' END AS Result

DECLARE @v1 INT = 9
DECLARE @v2 INT = NULL
DECLARE @v3 INT = 0

SELECT ISNULL(@v1, 0) + ISNULL(@v2, 0) + ISNULL(@v3, 0) AS Result

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.23 kb)