By: Joe Gavin | Updated: 2023-04-26 | Comments | Related: > TSQL
Problem
I want to understand what NULL values are in T-SQL and how to work with them in a SQL database.
- Are they the same as zero?
- Is it an unknown value?
- Can I use a NULL value in an arithmetic equation?
- Can I compare them the same as I would with regular comparison operators like equal to, greater than, less than, greater than or equal to, less than or equal to, not equal to, like, etc.?
Solution
We'll answer each of those questions. First, we'll define what NULL is and then we'll look at examples of using the following Operators and Functions to work with NULLs:
- IS NULL
- IS NOT NULL
- ISNULL()
- COUNT()
- MIN()
- MAX()
- SUM()
- AVG()
AdventureWorks2019 Database
The following queries have all been run in the AdventureWorks2019 database, you can download a copy of the database from this link: AdventureWorks Database Installation Steps.
All the examples are querying the SpecialOfferID, Description, MinQty, and MaxQty fields in the Sales.SpecialOffer table.
Definition of NULL
It's easier to start with what a NULL value is not. It's not equal to, greater than, or less than zero; it's not a non-printable space or a tab character you just can't see. NULL is simply an unknown or non-existent value.
For example, if a user's desktop PC does not power up, your Helpdesk's first question is likely, "Is it plugged in?" If the user can't see the back of the PC to determine if one end of the power cord is plugged into the power supply and the other end into an electrical outlet, the answer is NULL, as it may or may not be. You simply don't know. However, if they can look behind the PC, see the cord is plugged into the power supply, follow the cord to the other end and find out it's not plugged in, the answer is no.
Any attempt to use NULL in an arithmetic equation will result in NULL. For example, 1 + NULL = NULL, 1 – NULL = NULL, 1 * NULL = NULL, and 1 / NULL = NULL.
If we query the SpecialOfferID, Description, MinQty, and MaxQty fields in the Sales.SpecialOffer table, we'll see some values in the MaxQty column are NULL. Here is the syntax for the SELECT statement:
SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] ORDER BY [SpecialOfferID]; GO
What happens if we use Comparison Operators to compare a value to NULL in a SQL query?
SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] WHERE [MaxQty] = NULL ORDER BY [SpecialOfferID]; GO SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] WHERE [MaxQty] < NULL ORDER BY [SpecialOfferID]; GO SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] WHERE [MaxQty] > NULL ORDER BY [SpecialOfferID]; GO
Comparing the value of MaxQty equal to NULL, less than NULL, and greater than NULL, no records are returned. That's because we can't compare something to an unknown value. It's simply unknown.
Using IS NULL
Equals NULL will not return any records, as we saw above. IS NULL is an Operator that filters on records with a NULL field.
Here, we select all records where the MaxQty field is NULL as specified in the WHERE clause.
SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] WHERE [MaxQty] IS NULL ORDER BY [SpecialOfferID]; GO
Using IS NOT NULL
As you probably guessed, the IS NOT NULL operator will return all the records where a field has an actual value and is not NULL.
SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] WHERE [MaxQty] IS NOT NULL ORDER BY [SpecialOfferID]; GO
Using ISNULL()
The ISNULL() function, not to be confused with the IS NULL Operator, returns a specified value instead of NULL.
Here we will return a more meaningful value, 99999, which I chose instead of NULL to indicate that there is no MaxQty.
SELECT [SpecialOfferID] ,[Description] ,[MinQty] ,ISNULL([MaxQty], 99999) AS [MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer] ORDER BY [SpecialOfferID]; GO
Using COUNT() with NULL Values
The COUNT() function counts the number of records that meet a certain criterion.
This query will return the number of records with a value in the MaxQty field.
SELECT COUNT([MaxQty]) AS [MaxQtyFields] FROM [AdventureWorks2019].[Sales].[SpecialOffer]; GO
As expected the NULL fields are ignored, and only four records in the table are not NULL, so 4 is returned.
Using MIN() with NULL Values
MIN() displays the minimum record in a field. This query will ignore NULLs and return the minimum MaxQty value.
SELECT MIN([MaxQty]) AS [MinMaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer]; GO
The MaxQty values are 14, 24, 40, and 60. Fourteen is the minimum.
Using MAX() with NULL Values
MAX() displays the maximum record in a field. This query will ignore NULLs and return the maximum MaxQty value.
SELECT MAX([MaxQty]) AS [MaxMaxQty]] FROM [AdventureWorks2019].[Sales].[SpecialOffer]; GO
And sixty is the maximum of 14, 24, 40, and 60.
Using SUM() with NULL Values
SUM() adds up the records in a field. NULL fields are not included, so this returns the sum of 14, 24, 40, and 60.
SELECT SUM([MaxQty]) AS [SumMaxQty]] FROM [AdventureWorks2019].[Sales].[SpecialOffer]; GO
14 + 24 + 40 + 60 = 138.
Using AVG() with NULL Values
AVG() returns the average value of numbers in a field. NULLs are ignored and this query will return an average of 14, 24, 40, and 60.
SELECT AVG([MaxQty]) AS [AvgMaxQty]] FROM [AdventureWorks2019].[Sales].[SpecialOffer]; GO
(14 + 24 + 40 + 60) / 4 = 34 (type int is rounded down)
Next Steps
We've seen several simple examples so far, here are links to more articles and SQL tutorials related to SQL NULL values:
- Using the SQL ISNULL() Function
- How to Use SQL Server Coalesce to Work with NULL Values
- Join SQL Server Tables Where Columns Include NULL Values
- SQL WHERE IS NOT NULL for SELECT, INSERT, UPDATE and DELETE
- Some Tricky Situations When Working with SQL Server NULLs
- How To Retrieve the Last Non-NULL Value in SQL Server
- SQL Server NOT Equal Operators
- SQL ORDER BY Clause
- The Many Uses of Coalesce Function in SQL Server
- CONCAT and CONCAT_WS function in SQL Server
- What are the Aggregate Functions in SQL
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-04-26