By: Eric Blinn | 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;
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;
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 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;
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;
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;
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.
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;
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;
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;
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 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;
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 "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;
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
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
Article Last Updated: 2024-06-20
