SQL WHERE with NULL

How I do use the SQL WHERE clause for NULL values?

NULL is something you might see in the database quite a bit. NULL means there is no value. This is not a blank value – like a blank space – but truly nothing. This is a subtle but important difference. Those with some programming background will have some experience working with NULL values. (Ever get that irritating NULL value does not reference an object error when coding in C# or Java?)

It is possible to filter rows in the SQL WHERE clause by whether or not a NULL value appears. For example, to find all rows in the Person.Person table where the MiddleName value is not NULL:

SELECT *
FROM Person.Person
WHERE MiddleName IS NOT NULL

Notice how the filtering condition is the phrase “IS NOT NULL”. It will never be “<> NULL” which is the standard not equal to operator. NULLs require either “IS NULL” or “IS NOT NULL” which makes them unique to other data types.

To find all rows in the Sales.SalesOrderHeader table where the MiddleName column is NULL:

SELECT *
FROM Person.Person
WHERE MiddleName IS NULL

You can think of “IS NULL” as “equal to NULL” in some sense, but be sure to use the proper operator in your SQL WHERE clause. Just like we have done in other WHERE clause lessons, we can use the “AND” and “OR” operators to combine multiple filtering conditions. To find all rows in the Person.Person table where the MiddleName column has a NULL value or the Title column contains a non-NULL value:

SELECT *
FROM Person.Person
WHERE MiddleName IS NULL OR Title IS NOT NULL


Practice Problems

Complete the questions below to reinforce some of the concepts learned in this lesson about filtering NULL values using the SQL WHERE clause.

  1. Find all rows in the Sales.SalesOrderHeader table where the SalesPersonID value is NULL.
  2. Find all rows in the Production.Product table where the Color column is NULL
  3. Find all rows in the Production.Product table where the Color column is not NULL and the Size column is NULL.