Querying NULL Values in SQL

When querying a SQL database, you need to be aware of the presence of NULLs.  In SQL, a NULL represents an unknown, non-existent or empty value.  Sometimes the NULL value is put in explicitly or no values was specified for the column, so a NULL was entered.  The reason we need to be careful is we cannot query NULLs the way we do for data types.

For example, let's say that you wanted to query a customer's balance where it is zero (either the customer has paid his balance or he hasn't made any purchases).  Since this would be a money or other number type, you could run a query, like so, to get all your customers:

SELECT *
From CustomerTable
where Balance = 0

But, depending on how you set up your table, new customers may not have a value in the Balance field.  Therefore, this value could be a NULL.  The above query would not return a NULL value, so you aren't getting all the results you require.

There are two ways you can update your query.  Here is the first one:

SELECT *
From CustomerTable
where Balance = 0 or Balance IS NULL

To query on NULL values, you need to specify IS NULL or IS NOT NULL.  This is the only way to query NULLs directly.

The other way to query NULLs is the following.  Keep in mind that this will work, while also making your query NonSARGable:

SELECT *
From CustomerTable
where ISNULL(Balance, 0) = 0

ISNULL() is a function that allows you to convert NULLs in your field to a specified value.  ISNULL() is used like this:

ISNULL( column, value to replace NULL)

We replaced our NULLs with the value of 0.  We can then query all 0s using one simple clause.

To read more on NULL values, go here: NULL Values