In SQL, when writing queries to check if column values are NULL it is common to attempt WHERE conditions using ‘column_name = NULL’ or ‘column_name != NULL’ or ‘column_name <> NULL‘. Trying to check if a column is NULL using equal or not equal will actually provide incorrect results. This article will cover how to query for values that are NULL or not NULL. This article was written using MySQL as the database management system, but other relational DBMS’s deal with NULL the same or very similar.
What NULL Means In MySQL
In programming languages it is common for NULL to be considered a data type. This means that variables and values can be compared to NULL and a value of TRUE or FALSE will be returned.
In SQL, NULL is not used the same as in programming. In MySQL, NULL is a missing unknown value. Because NULL is considered missing or unknown any arithmetic comparisons (=, !=, >, =>, <, <=) made to NULL will return NULL instead of TRUE or FALSE.
Comparing Values To NULL In MySQL
To understand the results of comparisons to NULL it is first important to understand the results of comparisons to other values. In MySQL, 1 is returned if a comparison is TRUE and 0 is returned if a comparison is FALSE. This can be viewed in the below screenshot where a true statement of 5 = 5 returns 1 for TRUE and a false statement of 5 = 6 returns 0 for FALSE.
The below screenshot shows that when a comparison of 5 = NULL is done it returns NULL. NULL is returned because we do not know what the value of NULL is so it is not known if NULL is 5 or not so neither TRUE or FALSE are returned.
The below screenshot shows that even when NULL is compared to NULL it will result in NULL being returned. This is because we do not know the value of either NULL in the comparison so the result is neither TRUE or FALSE.
The below screenshot shows that comparing NULL to TRUE or FALSE will return NULL.
It is also important to know that an empty string is not the same as NULL. When an empty string is compared to NULL then NULL is returned. However, when an empty string is compared to an empty string then a 1 for TRUE is returned.
IS NULL And IS NOT NULL Operators
To illustrate how to write queries to check for NULL values lets use the below data that has some NULL values for the phone column.
For the first example lets say that we want to get all employees that our company is missing data for their phone number so that we can email them to get this information.
As mentioned previously it is common to believe the correct way to get this data is to use a WHERE phone = NULL condition. This will give the incorrect result of an empty set as shown in the screenshot below. This is because any arithmetic comparison to NULL will never return TRUE, it will always return NULL so no rows match this query.
The correct way to test if a value is NULL is to use the IS NULL operator. To get a result set of all employees with NULL in the phone column, the condition WHERE phone IS NULL needs to be used as shown in the screenshot below. In this query, anytime NULL is the data for the phone column then 1 is returned for TRUE and that row will be in the result set.
For a second example, lets say that we need to call all employees. If we do not have an employee’s phone number then we can not call them so we want to write a query to find all employees that have a phone number in the phone column.
Just like using WHERE phone = NULL was incorrect in the previous query, trying WHERE phone != NULL as the condition will return an incorrect empty set. This is shown in the screenshot below.
The correct way to test if a value is not NULL is to use the IS NOT NULL operator. To get a result set of all employees that do not have NULL in the phone column, the condition WHERE phone IS NOT NULL needs to be used as shown in the screenshot below. In this query, anytime NULL is the data for the phone column then 0 is returned for FALSE and that row will not be in the result set.
Conclusion
In MySQL, NULL is considered a missing unknown value. Comparing any value to NULL with the comparison operators(=, !=, <>, <, <=, >, >=) will return NULL. To query for NULL values use the IS NULL or IS NOT NULL operators.
References:
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null
https://www.youtube.com/watch?v=laSMjrziNHU
https://www.guru99.com/null.html