As we have seen, NULL is a special value in SQL that denotes the lack of data in a table.

For example, if a site doesn’t store the last names of its users and later adds a field for the last names in user profiles, the existing users may have NULL in their last_name field until it is updated.

A value of NULL is not the same as:

• A value of 0
• An empty string ""
• The string "NULL"

NULL values work in a different way than other SQL datatypes. For example,

• NULL does not interact normally with logical expressions. For example, NULL = NULL evaluates to false.
• By default, aggregate functions ignore it. For example, the value of AVG((1,2,NULL,3)) will be 6/3=2, not, 6/4=1.5
• JOIN clauses do not match NULL values with NULL values.
• The keyword DISTINCT does not omit NULL values.
• SELECT COUNT(*) FROM ... counts every row, including the ones with null values.
• SELECT COUNT(column_name) FROM ... counts only non-null rows

Because of this, we have special clauses that allow us to handle NULL values.

IS NULL and IS NOT NULL

The clause IS NULL(<expression>) returns true when the value of <expression> is NULL. This can be used in combination with the CASE clause to handle NULL values.

We also have the clause IS NOT NULL(<expression>), that works inversely.

IFNULL

The function IFNULL lets us modify a value in case it is NULL.

Let’s say we want to return an empty string for every user_email field that has a NULL value. In this case, we cannot use a CASE clause because NULL = NULL evaluates to false.

However, we can achieve this by using the IFNULL function in the following way:

IFNULL(user_email, "")

The general syntax for IFNULL is the following:

IFNULL(<expression>, alt_value)

The query above returns the value of <expression> unless it is NULL, in which case it returns the alternate value alt_value.

NULLIF

Let’s say we want to count all users that have a valid value for their last_name field.

We know our database only started recording the last_name field for sign-ins a few months ago.

After the update, the database started writing an empty string in the last_name field for all the users who didn’t input their last names. All of the users who created their accounts before the update still have a NULL value in the last_name field.

As we said, the COUNT clause omits NULL values, but it does count empty strings. If we just call COUNT(last_name), we will count all the empty strings that don’t correspond to users who did input a last_name.

In this case, we would like to change all our empty strings to NULL values, so that they are not counted.

We can do this with the following expression:

COUNT(NULLIF(last_name, ""))

The NULLIF function compares two expressions and returns NULL when they are equal. If not, it returns the value for the first expression. In this case, it returns NULL if the last name is an empty string, and the value for last_name otherwise.

In general, the syntax for NULLIF is:

NULLIF(<expression1>, <expression2>)

If expression1 is equal to expression2, it returns NULL. If they are different, it returns expression1.