SQL IS NULL And IS NOT NULL Operators

Introduction

When dealing with NULLs, most beginners think it has some value, but it means unknown.

This article will explore what NULL is and SQL's NULL conditional operators.

These operators are NULL and are NOT NULL.

What is NULL?

It is used to specify that a particular data value does not exist.

That's why you'll hear from other SQL developers or administrators; NULL represents a missing or unknown value. And from that statement, I think it can be easily remembered and understood.

Moreover, the ANSI behavior for NULL values any expression containing a NULL value is NULL.

Let's see some examples below.

Examples

1. Let's try to add a number to a NULL value.

SELECT (NULL + 1) [Sum of NULL and 1] -- NULL

Output

2. Let's try to concatenate a string to a NULL value.

SELECT (NULL + 'C# Corner Rocks') [Concat NULL to a word] -- NULL

Output

IS NULL and IS NOT NULL

I still remember learning TSQL when I wanted to check a column for NULL. I used the equal (=) or the not (<>) operator.

Let's see an example below.

SELECT * FROM [tblSomething] WHERE name = NULL
SELECT * FROM [tblSomething] WHERE name <> NULL

Note: the example code above won't behave as expected. You won't see any results!

Checking NULL values is impossible with comparison operators such as =, < or <>.

It is because this is not its default behavior of it.

That's why instead of using these equal and not operators, we can use IS NULL or IS NOT NULL instead.

Let's see an example below.

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits 
(Id INT IDENTITY, 
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description) 
 VALUES 
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results
 
 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

As you can see, in our code sample above, we have combined the equal and the not operator with their NULL operators' counterparts.

Let's try to see the differences.

First, instead of the = operator, we used the IS NULL operator to check whether a column is NULL.

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results

Output

Second, instead of the <> operator, we used the IS NOT NULL operator to check whether a column is not NULL.

SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

Output

SET ANSI_NULLS ON or OFF

Hopefully, now you understand that by default, you can't use the equality operators when dealing with NULLs and instead use IS NULL or IS NOT NULL operators.

However, this default ANSI behavior can be turned off by adding this line of instruction SET ANSI_NULLS OFF before executing the entire previous code.

Let's add the SET ANSI_NULLS OFF on our previous example and see the output.

SET ANSI_NULLS OFF; -- LET'S TURN OFF THE DEFAULT ANSI BEHAVIOR!

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits 
(Id INT IDENTITY, 
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description) 
 VALUES 
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 3 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 result
 
 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 2 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 result

Output

As you can see from the output, when you set ANSI_DEFAULT off, the equality operators will eventually be acceptable and work as expected.

So, in case you experienced this, there's a higher chance that the ANSI_DEFAULT is turned off.

Avoid Changing Default ANSI_NULLS

Turning off the ANSI_NULLS option is not recommended unless you're just trying something or making some quick fixes.

Moreover, using the IS NULL and IS NOT NULL operators for the NULL value comparison is safer because it will work in all cases irrespective of the ANSI_NULLS option setting.

Summary

This post taught us the concepts of NULL, IS NULL and IS NOT NULL operators. Moreover, we have tackled the ANSI_NULLS and given some insights.

I hope you have enjoyed this article, as I have enjoyed writing it.

Stay tuned for more.

Many thanks; until next time, happy programming!