What Is NULL in SQL Server? The Value That Isn’t a Value

NULL in SQL Server is the one thing every beginner thinks they understand and every senior person has been burned by. It is not zero. It is not an empty string. It is not a blank. NULL means the database does not know the value, and that one idea changes how every comparison in your query behaves.

Three Jars on a Shelf

Picture three jars. The first is full of rice. The second holds one grain. The third is empty. Now a fourth jar arrives with no label and the lid taped shut, and nobody can open it. That last jar is NULL. It is not empty. You simply do not know what is inside.

That distinction matters because the empty jar and the unknown jar behave differently in every query you write. An empty string is a value. Zero is a value. NULL is the absence of one.

Arithmetic Gives Up

Anything you do with an unknown produces an unknown. This runs on any version of SQL Server:

SELECT 1 + NULL AS one_plus_null;

one_plus_null
NULL

Not 1. Not an error. NULL. If you do not know how many apples are in the box, adding one apple still leaves you not knowing.

Equals Never Matches It

This is the rule that catches people. NULL is not equal to NULL, because two unknowns cannot be proved the same.

SELECT CASE WHEN NULL = NULL
            THEN 'equal'
            ELSE 'not equal or unknown'
       END AS null_eq_null;

null_eq_null
not equal or unknown

So WHERE Nickname = NULL returns nothing, ever. The correct form is IS NULL and IS NOT NULL. Those two are the only operators that can look inside the taped jar.

Counting Tells You the Truth

Here is a small table with one real value, one NULL and one empty string. I ran this on SQL Server 2025 while writing this post:

DECLARE @t TABLE (id int, nickname varchar(20));
INSERT @t VALUES (1, 'Pat'), (2, NULL), (3, '');

SELECT COUNT(*) AS count_star,
       COUNT(nickname) AS count_col,
       SUM(CASE WHEN nickname IS NULL THEN 1 ELSE 0 END) AS nulls,
       SUM(CASE WHEN nickname = '' THEN 1 ELSE 0 END) AS empties
FROM @t;

count_star  count_col  nulls  empties
3           2          1      1

COUNT(*) counts rows. COUNT(nickname) counts rows where that column has a value, so it returns 2. The NULL and the empty string are two different rows. If your report ever disagrees with itself by a few rows, this is usually why.

The Row That Disappears

Now ask for everyone who is not called Pat:

SELECT id FROM @t WHERE nickname <> 'Pat';

id
3

Row 2 is missing, and most people expect to see it. The database cannot prove that an unknown nickname differs from Pat, so the row is not returned. To include it you have to say so: WHERE nickname <> ‘Pat’ OR nickname IS NULL.

The NOT IN Trap

This is the one that ruins an afternoon. A single NULL inside a NOT IN list makes the whole thing return no rows at all.

DECLARE @b TABLE (v int);
INSERT @b VALUES (1), (NULL);

SELECT COUNT(*) AS rows_returned
FROM (VALUES (1), (2), (3)) AS a(v)
WHERE a.v NOT IN (SELECT v FROM @b);

rows_returned
0

You expected 2 and 3. You got nothing. Is 2 not in the list? SQL Server checks 2 against 1, which is true, then 2 against NULL, which is unknown, and unknown poisons the answer. Use NOT EXISTS instead, which handles this correctly, or filter the NULLs out of the subquery first.

Handling NULL on Purpose

Two functions cover almost everything. ISNULL takes two arguments and swaps a NULL for a replacement. COALESCE takes as many as you like and returns the first one that is not NULL.

SELECT ISNULL(nickname, 'no nickname') AS display_name FROM @t;

SELECT COALESCE(nickname, 'no nickname') AS display_name FROM @t;

Use COALESCE by default. It is the standard form, it accepts a list, and it works the same way on other database engines.

Where NULL Is Allowed to Live

A primary key column can never hold NULL. A unique constraint is more relaxed and permits one NULL, because a second one would have to be proved equal to the first, and it cannot be. A foreign key column holding NULL simply means the row points at nothing yet, and that is legal.

The design question is the one worth thinking about. Every nullable column is a promise that the value may genuinely be unknown. If a column should always have a value, mark it NOT NULL and let the database enforce it. Every NULL you allow is a branch somebody has to handle later.

I still meet NULL problems on client servers most weeks. They almost never look like a NULL problem. They look like a report that is short by four rows.

NULL is not an empty value, it is the database admitting it does not know.

This post was rewritten from scratch in September 2026. The original, published on 2007-05-21, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.