17. Checking for NULL

In our Database Basics section, we briefly covered NULL values. One of the key takeaways from that section is that all comparisons involving NULL resolve to NULL. If that's the case, how do we ever check (e.g., in a where clause) if something is or is not null? SQL has us covered with special operators: is NULL and is not NULL.

is NULL

This operator returns true if the value being compared is NULL and false otherwise. Here are some examples:

• select * from transactions where transaction_date is NULL
• case when account_id is NULL then 'Blank Value' else account_id end

To put it very simply, if you are checking for NULL, just use is in place of = and you're all set.

is not NULL

This operator does the opposite: if the value being compared is NULL it returns false, and returns true otherwise. Some examples:

• select * from emails where email_address is not NULL
• case when amount is not NULL then amount else 0 end

The simple rule here is when you're checking that something is not NULL, replace <> with is not.

Exercise 1: Checking for NULL

Write an SQL query that selects all columns from the accounts table where the first name is not null.