Be careful when doing comparisons on an attribute whose value may be NULL in SQL

I write this quick note to remind myself to be careful when working with comparison operators on an attribute that may take NULL value.

For example we have a table USER storing the survey users with the attribute u_issubmitted that takes the value 1 when the user has submitted the survey, and NULL when the user has not submitted the survey.

We assume that there are 59 users in the table USER, 51 of them have submitted the survey and 8 of them have not submitted the survey. Let's try to run some SQL statements on the table USER.

The following SQL statement

select count(*) from users;

returns 59 as we expected.

The following SQL statement

select count(*) from users where u_issubmitted = 1;

returns 51 as we expected.

However, the following both two SQL statements do not return value 8 as we expected, instead, value 0 is returned. These statements look correct logically but they are technically incorrect.

select count(*) from users where u_issubmitted <> 1;

select count(*) from users where u_issubmitted != 1;

The correct SQL statement to count the number of users who have not submitted the survey is as following

select count(*) from users where u_issubmitted is NULL;

The above statement returns value 8 as we expected.

In conclusion, we should be careful when working on the attributes that may take value NULL. You can read more about how MySQL deals with NULL values in comparisons here.