Learn about SQL NULL for Values, Querying Data and Functions

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.

Attempt to Compare With an Unknown Value

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:

Resources

Community

Subscribe