<< SQL Server Hosting - HostForLIFE :: SQL CAST And CONVERT Function | SQL Server Hosting - HostForLIFE :: Create New Database From Existing Database In Same Microsoft SQL Server >>

SQL Server Hosting - HostForLIFE :: SQL IS NULL And IS NOT NULL Operators

February 15, 2022 07:22 by author

Peter

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

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

These operators are IS NULL and IS 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

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 be behaving as expected. You won't see any results!

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

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

That's why instead of using these equal and or 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 = 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 previous entire 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 turn set ANSI_DEFAULT off, the equality operators will eventually be acceptable and work as expected.

So, in incase 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, it is safer to use the IS NULL and IS NOT NULL operators for the NULL value comparison because it will work in all cases irrespective of the ANSI_NULLS option setting.