By: Eric Blinn | Updated: 2024-06-20 | Comments (1) | 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.](/tipimages2/8026_sql-null-meaning-1.png)
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](/tipimages2/8026_sql-null-meaning-2.png)
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.](/tipimages2/8026_sql-null-meaning-3.png)
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.](/tipimages2/8026_sql-null-meaning-4.png)
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](/tipimages2/8026_sql-null-meaning-5.png)
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.](/tipimages2/8026_sql-null-meaning-6.png)
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.](/tipimages2/8026_sql-null-meaning-7.png)
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.](/tipimages2/8026_sql-null-meaning-8.png)
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.](/tipimages2/8026_sql-null-meaning-9.png)
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.](/tipimages2/8026_sql-null-meaning-10.png)
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.](/tipimages2/8026_sql-null-meaning-11.png)
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.](/tipimages2/8026_sql-null-meaning-12.png)
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).](/tipimages2/8026_sql-null-meaning-13.png)
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.](/tipimages2/8026_sql-null-meaning-14.png)
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
![MSSQLTips author Eric Blinn](/images/Blinn-Eric.jpg)
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