In a previous post, we discussed how to write a select statement. Over the next several posts we will discuss how to use the WHERE keyword to filter records by numbers, character data, dates and times, and NULL values. This post specifically will focus on filtering records by NULL values.

--Searching for values that do NOT exist
SELECT ProductID, Name, Color
FROM Production.Product
WHERE COLOR IS NULL

--Searching for values that do exist
SELECT ProductID, Name, Color
FROM Production.Product
WHERE COLOR IS NOT NULL

To start we must understand that a NULL value is an absence of a value. So, there is nothing it can be equal to as it has no value. So instead of using an equal (=) sign we specify whether a field IS NULL or IS NOT NULL.

In the code above, we use both examples of the WHERE statement. The first is used to find all the products that do not have a value in the Color column. (For this example, we are using the AdventureWorks database and the Production.Product table. ) We should see that 248 columns are returned, and that the column field displays NULL for each record that did not have a color value.

When we run the code again, but this time with the statement WHERE Color IS NOT NULL, we will notice a different result. This time 256 records were returned, and each product does have a value in the Color column.