SQL IS NULL and SQL IS NOT NULL Examples

By: Eric Blinn   |   Updated: 2024-11-15   |   Comments (2)   |   Related: > TSQL

Problem

Many data professionals search for help writing T-SQL queries containing columns with NULL values in a Microsoft SQL Server table. Some of the most common NULL functions include IS NULL, IS NOT NULL, NOT NULL, NULL, what is NULL, NULL vs NOT NULL, etc. In this SQL tutorial, the goal is to help you better understand how to work with NULL values in a SQL database.

Solution

This SQL tutorial will provide answers explaining the IS NULL and IS NOT NULL SQL statements in a SQL Server relational database (RDBMS).

All demos are executed using SSMS and SQL Server 2022, but the information contained in this tip is valid going back many, many versions of SQL Server.

What is NULL?

NULL is the absence of a value in a place where one might expect a value or an unknown value. Examples of this would be in a column in a SQL Server table or in a variable as part of a T-SQL procedure or script. This is typically a difficult situation to explain so we'll jump right into a demo to try to make sense of it.

Imagine this table:

--From MSSQLTips.com
CREATE TABLE Samples (
  SomeNumericValue INT
, SomeAlphaValue VARCHAR(10)
, SomeDateValue DATE(3));

Insert some data with the following syntax:

INSERT INTO Samples(SomeNumericValue, SomeAlphaValue, SomeDateValue)
VALUES (5, 'HELLO', '20-Oct-2024');

The table has 3 columns. Someone querying this table would expect 3 values on each row. We placed a value in all 3.

But, what if we make another row in the same table and don't put a value in 1 of the columns?

INSERT INTO Samples(SomeNumericValue, SomeDateValue)
VALUES (6, '21-Oct-2024');

This time, SomeAlphaValue was skipped. It has no value. Someone who queries this table later would expect a value in the SomeAlphaValue column, but we didn't provide one.

What will this person who later queries the table see?

SELECT * FROM Samples; -- SELECT statement

Since we didn't provide a value for SomeAlphaValue, SQL Server shows a NULL in the query results where that column value was expected to be. This NULL indicates the absence of a value. We provided nothing in the insert statement. It's not the same as an empty string for character data types. It's not the same as 0 for numbers, or 1-Jan-0001 for most date data types. Those are all values. NULL indicates the absence of any value.

--From MSSQLTips.com
--Remember this script as it will be used again later in the tip.
DELETE Samples;
 
--All 3 columns are receiving a value.  They are all small, but they are all there.
INSERT INTO Samples(SomeNumericValue, SomeAlphaValue, SomeDateValue)
VALUES (0, '', '1-Jan-0001');
 
--These next 3 each skip one column.  
--The skipped column which receives NO VALUE will show a NULL in the row.
INSERT INTO Samples(SomeAlphaValue, SomeDateValue)
VALUES ('TEXT', '23-Oct-2024');
 
INSERT INTO Samples(SomeNumericValue, SomeDateValue)
VALUES (7, '24-Oct-2024');
 
INSERT INTO Samples(SomeNumericValue, SomeAlphaValue)
VALUES (8, 'TEXT');
 
SELECT * FROM Samples; -- SQL Query

It is important to understand that the word NULL has not been stored in these column values. We can tell this is the NULL value in SSMS, rather than the string 'NULL' because the background is a shade of yellow.

Here is a strange example where I put the literal word "NULL" in the string column during one insert and explicitly insert a NULL value in the second. Notice that NULL, by itself without quotes, is a valid entry. It will be treated the same as if I had not included the target column in the list of columns in the insert statement.

--From MSSQLTips.com
DELETE Samples;
 
--The word NULL is different than the NULL value.
INSERT INTO Samples(SomeNumericValue, SomeAlphaValue, SomeDateValue)
VALUES (9, 'NULL', '25-Oct-2024');--YES QUOTES ON NULL
 
INSERT INTO Samples(SomeNumericValue, SomeAlphaValue, SomeDateValue)
VALUES (10, NULL, '26-Oct-2024');--NO QUOTES ON NULL
 
SELECT * FROM Samples;

In the above screenshot, the box labeled 1 has the string "NULL" in it. The background is white. Row 2 is the NULL value many of us have seen in SSMS. In all my years as a DBA, I've never had a query result like row 1 on purpose. Any time I've seen that it's either a demo like this one or a code mistake somewhere.

The "IS" Operator

The NULL value is special in SQL Server. It cannot be searched for or compared using normal operators like =, <, >, BETWEEN, or LIKE. The only operator that will work properly with NULL values is "IS". "IS" is used exclusively to check for NULL values and cannot be used in any other type of comparison.

Looking for NULL Values Using IS NULL Operator

When looking for values that ARE null, use the "ColumnOrVariable IS NULL" argument. This query is an example of that and more will follow.

SELECT * FROM Samples WHERE SomeAlphaValue IS NULL;

Looking for NON-NULL Values Using IS NOT NULL

When looking for values that ARE NOT NULL, use the "ColumnOrVariable IS NOT NULL" argument. Further examples will appear in the following demos.

SELECT * FROM Samples WHERE SomeAlphaValue IS NOT NULL;

Looking for NULL Values Using SQL IS NULL in a WHERE

For this demo, I am going to return the Samples table to the state from earlier where it has 4 rows. That script is commented above to indicate it will be reused. Recall that there is one row where each of the 3 columns contains a NULL value. Consider this set of queries.

SELECT * FROM Samples WHERE SomeAlphaValue = NULL;
SELECT * FROM Samples WHERE SomeNumericValue = NULL;
SELECT * FROM Samples WHERE SomeDateValue = NULL;

Running these 3 queries will return 3 empty data sets. This is because the NULL value cannot be compared using the = (equals) operator. A column or variable will NEVER equal NULL.

Instead, use the IS operator. It is also important to remember that NULL, by itself, is a SQL keyword. Do not put quotes on the word NULL or SQL Server will look for the word NULL rather than a NULL value.

SELECT * FROM Samples WHERE SomeAlphaValue   IS NULL;
SELECT * FROM Samples WHERE SomeNumericValue IS NULL;
SELECT * FROM Samples WHERE SomeDateValue    IS NULL;

These 3 queries return one row each where the column in question contains the NULL value.

Looking for Non-NULL Values Using IS NOT NULL in WHERE

Continuing from the prior section, to look for rows where a column is any value other than NULL (which is to say, any value at all), simply include the keyword NOT after the operator IS.

SELECT * FROM Samples WHERE SomeAlphaValue IS NOT NULL;
SELECT * FROM Samples WHERE SomeNumericValue IS NOT NULL;
SELECT * FROM Samples WHERE SomeDateValue IS NOT NULL;

Using IS NULL or IS NOT NULL in CASE

When working with NULL values, the operation of a CASE statement will work the same as in a WHERE clause. The operator IS must be used rather than = (equals).

--From MSSQLTips.com
DECLARE @MyNumericVariable INT;
DECLARE @MyStringVariable VARCHAR(100);
 
--Since we never set these variables to any value, they are
--going to be evaluated as NULL.
 
SELECT 
--2 IS NULL examples
  CASE WHEN @MyNumericVariable IS NULL THEN -1 ELSE @MyNumericVariable END AS MyNumericVariable
, CASE WHEN @MyStringVariable IS NULL THEN 'It was never set.' ELSE @MyStringVariable END AS MyStringVariable
--2 IS NOT NULL examples
, CASE WHEN @MyNumericVariable IS NOT NULL THEN @MyNumericVariable ELSE -1 END AS MyNumericVariable
, CASE WHEN @MyStringVariable IS NOT NULL THEN @MyStringVariable ELSE 'It was never set.' END AS MyStringVariable;
 
--Now we set them and they should act in reverse.
--Notice that while we use an empty string, that is a value that is NOT NULL.
SET @MyNumericVariable = 99;
SET @MyStringVariable = '';
 
SELECT CASE WHEN @MyNumericVariable IS NULL THEN -1 ELSE @MyNumericVariable END AS MyNumericVariable
, CASE WHEN @MyStringVariable IS NULL THEN 'It was never set.' ELSE @MyStringVariable END AS MyStringVariable
, CASE WHEN @MyNumericVariable IS NOT NULL THEN @MyNumericVariable ELSE -1 END AS MyNumericVariable
, CASE WHEN @MyStringVariable IS NOT NULL THEN @MyStringVariable ELSE 'It was never set.' END AS MyStringVariable;

Using IS NULL or IS NOT NULL in WHILE

A T-SQL program is able to check for NULL or NOT NULL values in the argument at the top of a WHILE loop. Here is an example using IS NULL in a WHILE loop. This code is not based on a real table and is just for illustrative purposes.

--From MSSQLTips.com
DECLARE @CompletionText VARCHAR(100);
 
--Since we never set this variable to any value,
--it is going to be evaluated as NULL.
--This loop will complete one order at a time 
--until there are none left as indicated by a value
--in the @CompletionText variable.
WHILE @CompletionText IS NULL
BEGIN
  EXEC dbo.CompleteOneOrder @CompletionText = @CompletionText OUTPUT;
END;

Final Thoughts

Learning how to handle NULL values in queries is a skill every T-SQL writer should have. I hope this tip helped in that regard!

Next Steps

• Learn more about NULL values in SQL Server
• Using ISNULL, NULLIF, and COALESCE
• Using COLASCE to handle NULL values
• More about COALESCE
• Learn the most common SQL statements:
  • SELECT Statement
  • INSERT Statement
  • UPDATE Statement
  • DELETE Statement
• Subquery Logic
• Transact-SQL Quick Reference Guide
• Primary Key and Foreign Key
• SQL Logical Operators ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, SOME

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips