What does SQL NULL mean and how to handle NULL values

By:   |   Updated: 2024-06-20   |   Comments (2)   |   Related: > TSQL


Problem

I'm constantly seeing the NULL in column values. What does that mean? It seems to make some of my queries act strange, and I need to understand it better.

Solution

This tip will help you understand what NULL means in the context of the Microsoft Fabric data universe, which includes SQL Server. It will then show how these values can make queries act unexpectedly and how to best interact with NULL values to avoid problems.

What is NULL?

NULL isn't a value but rather the absence of a value. That doesn't always make sense to a newcomer. Let's use an example to explain.

I asked four people how much money they had in their wallets. The first person said $2. The second person answered $10. The third person answered $0, no money in their wallet. The fourth person answered that they didn't even have a wallet. If I were recording these answers in a database, it would be disingenuous to record that this fourth respondent had no money in their wallet when they didn't have a wallet at all. This last person's answer is the equivalent of NULL. It's the absence of an answer to the question.

The code below is a SQL Server representation of the story told above.

--From MSSQLTips.com
CREATE TABLE CASH_IN_WALLET (Person VARCHAR(10) NOT NULL, Amount MONEY NULL);
INSERT INTO CASH_IN_WALLET VALUES('Eric', 2);
INSERT INTO CASH_IN_WALLET VALUES('Andy', 10);
INSERT INTO CASH_IN_WALLET VALUES('Bob', 0); --Bob has a wallet.  It's empty.
INSERT INTO CASH_IN_WALLET VALUES('Jason', NULL); --Jason does not have a wallet at all.
SELECT * FROM CASH_IN_WALLET;
The yellow background of the word NULL is how you can tell that the column contains the NULL value and not the string NULL.

Variables hold the value NULL when declared and before being set to any value. In this case, NULL, again, indicates the absence of any value.

DECLARE @MSSQLTips VARCHAR(100);
SELECT @MSSQLTips;
This NULL value is

It is important to understand that NULL is not the same as 0 for numbers or an empty string for character-based data types. It is also not the word "NULL." NULL is completely separate and follows different rules than non-null values.

ISNULL Function

The ISNULL function will be used throughout this tip. It seemed best to introduce it at the top. It accepts two parameters: a value and a replacement. The function attempts to return the first value, but if that value is NULL, then the second parameter, the replacement, is returned instead.

In this example, the variable is never set and remains NULL. The ISNULL function sees that the variable is NULL and returns the replacement instead.

DECLARE @MSSQLTips VARCHAR(100);
SELECT  ISNULL(@MSSQLTips, 'It has not been set yet') VariableValue;
The result contains the replacement text.

The next example's variable is set to a value. It wouldn't matter what that value was. It could even be an empty string. Since there is a value in the variable, it is not NULL. Now, the ISNULL function returns that value and ignores the potential replacement text.

DECLARE @MSSQLTips VARCHAR(100);
SET @MSSQLTips = 'I have set it.';
SELECT  ISNULL(@MSSQLTips, 'It has not been set yet') VariableValue;
The result contains the test from the variable and the replacement text is nowhere to be seen.

Another function largely synonymous with ISNULL is COALESCE, which some people prefer. I'll be using ISNULL in this tip, but you can read about the differences between the two in the following tip: Deciding between COALESCE and ISNULL in SQL Server.

NULL in Aggregate Queries

Aggregate functions treat NULL values differently than other values. When using the COUNT function, NULL values are ignored. Remember: There are four rows in the CASH_IN_WALET table. In this snippet, notice that the count of the Amount column that has one NULL value is only 3.

SELECT 
  COUNT(*) CountStar
, COUNT(Amount) CountAmount
FROM CASH_IN_WALLET;
CountStar is 4, but CountAmount is only 3

The average function, AVG, also ignores NULL values. The wallets have $12 total divided across four rows. One might expect the average to be $3, but it is not. Since the NULL value is not considered, the $12 is only divided across the three non-null rows and returns $4.

SELECT
  SUM(Amount) TheSUM 
, AVG(Amount) TheAVG
FROM CASH_IN_WALLET;
TheSUM is 12 as expected, but TheAVG is 4.

The common warning below will appear in the messages tab when an aggregate function skips a NULL value. In most circumstances, this message can be ignored.

This warning reads Null value is eliminated by an aggregate or other SET operation.

We will see changes in the output if we apply the ISNULL function to the Amount column to exchange NULL values for zeroes before applying the aggregate. First, the average now considers all four rows and returns $3. Second, the messages tab is also clear of any warnings since the NULL values were removed before the aggregation began.

SELECT 
  SUM(ISNULL(Amount, 0)) TheSUM 
, AVG(ISNULL(Amount, 0)) TheAVG
FROM CASH_IN_WALLET;
TheSUM remains 12, but TheAVG drops to 3.

The next demo removes the $0 row from the CASH_IN_WALLET table.

DELETE CASH_IN_WALLET WHERE Amount = 0;

One might expect the minimum Amount value to be the NULL value. Or maybe a zero. Certainly, the person who doesn't even have a wallet is the person with the least amount of money! But, once again, the aggregate function ignores the NULL value and returns the smallest non-null value, $2.

SELECT 
  MIN(Amount) TheMIN
FROM CASH_IN_WALLET;
TheMIN is 2.

Depending on the scenario, it may make sense to turn the NULL values into another value, often zero. The following query does just that.

SELECT 
  MIN(ISNULL(Amount, 0)) TheMIN
FROM CASH_IN_WALLET;
Now TheMIN is 0.

NULL in String Manipulation

Another time that NULL values can cause havoc is when working with string data. This query declares three strings and attempts to concatenate them.

DECLARE @String1 VARCHAR(100) = 'Hello';
DECLARE @String2 VARCHAR(100);
DECLARE @String3 VARCHAR(100) = 'World';
 
SELECT @String1 + @String2 + @String3 StringOutput;
The StringOutput is NULL rather that including any of the text from the variables.

The output does not say "HelloWorld" but instead returns a NULL string. This is because the middle string, @String2, remains a NULL value, meaning that any concatenation that includes @String2 will also be NULL.

If you're following along and your demo did NOT do that, there is a reason. There is a connection-level setting that can be changed to tell SQL Server to treat NULL values like empty strings when doing concatenation. If you run this statement and rerun the previous demo, you'll see this output with the HelloWorld string. If you already saw this output on the first execution, change the OFF to ON and rerun the demo. Now, you should see the NULL value in the results.

SET CONCAT_NULL_YIELDS_NULL OFF;
Now the StringOutput does say, HelloWorld.

Changing this setting to OFF is functionally equivalent to changing the code but with less typing.

SELECT ISNULL(@String1, '') + ISNULL(@String2, '') + ISNULL(@String3, '') StringOutput; 

NULL in Comparisons

The NULL value cannot be compared using normal operators. Consider the query below. We have been working with this table and know that one row has a NULL in the Amount column, yet our query returns zero rows.

--MSSQLTips.com -- Purposely failing query
SELECT * 
FROM CASH_IN_WALLET
WHERE Amount = NULL;
The messages tab says (0 rows affected).

The "equals" operator is only functional for non-null values. To search specifically for NULL values, we must use the IS operator instead.

SELECT * 
FROM CASH_IN_WALLET
WHERE Amount IS NULL;
The query returns 1 row.  It is the row for Jason with a NULL for the amount.

To search for all non-null values, the keyword NOT is added.

SELECT * 
FROM CASH_IN_WALLET
WHERE Amount IS NOT NULL

There is more to learn about NULLs in comparisons than can be covered in this tip. Read more about it in this tip: SQL Server NOT Equal Operators.

Final Thoughts

NULL is a value that pops up a lot in SQL Server. Having a solid understanding of how your queries will interact with it is vital to being a good DBA, SQL Programmer, Report Writer, or any other profession that involves structured data.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eric Blinn 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


Article Last Updated: 2024-06-20

Comments For This Article




Monday, July 8, 2024 - 4:06:38 AM - Ronald Kunenborg Back To Top (92367)
IMHO it's a good article. Many real world databases have been built by programmers and enthusiasts, without much formal training. So NULLs are a fact of life that you need to be able to deal with.

However, I'd like to point out that seeing NULL values is usually a result of database design failures. If you account for the fact that people don't have wallets, you would have two subtypes (one for people with wallets, and one for people where we don't know if they have money) and only one of the subtype tables would have a field for money.

I recommend reading dbdebunk.com on this topic, specifically https://www.dbdebunk.com/2013/07/the-final-null-in-coffin-relational.html.

But this is in addition to the article, because NULLs are a fact of life.

Thursday, June 20, 2024 - 1:22:17 PM - Doug Bishop Back To Top (92328)
While basically not incorrect, technically it would be best to state that NULL is an unknown. Jason could have a wallet but he left it at home. In that case, there is something in his wallet, we just don't know what it is. We see this a lot with middle names/initials. NULL doesn't mean the person doesn't have a middle name, they probably just didn't provide it, thus it is unknown. It could be that they don't have a middle name (My dad and one of my brothers don't), or they just didn't provide it. It is unknown.














get free sql tips
agree to terms