"NOT IN" behavior affecting NULL column display

Question

I have a table where I record purchases made by the site.

I use a field called status_plataforma to receive the status of the Paypal transaction. This field, besides being able to receive an integer, by default it is NULL.

In a certain query, I needed to know if the field status_plataforma is not with the value 6 or 7. For that, I’m using the condition WHERE status_plataforma NOT IN(6, 7). However, when this field is at the value NULL, the result is not found!

I mean, I don’t have 6 nor 7 in that column, but only NULL and, theoretically, the result should be returned.

My Trials:

Example without NOT IN:

mysql> select id, preco, status_plataforma, concluida  FROM compras WHERE site_cadastro_aluno_id = 43;
+----+-------+-------------------+-----------+
| id | preco | status_plataforma | concluida |
+----+-------+-------------------+-----------+
| 41 | 29.90 | NULL              |         0 |
+----+-------+-------------------+-----------+
1 row in set (0,00 sec)

The problem happens when I use NOT IN:

mysql> select id, preco, status_plataforma, concluida  FROM compras WHERE site_cadastro_aluno_id = 43 AND status_plataforma NOT IN (6,7);
Empty set (0,00 sec)

As seen in the previous consultation, in fact status_plataforma is not even 6 nor 7, but yes NULL.

Why the NOT IN had this behavior?

Answer 1

As discussed in Why NULL values are not selected?, NULL is not a value, so there is no way to compare it with other values - including itself.

For example, even if you did

select id, preco, status_plataforma, concluida FROM compras WHERE status_plataforma = NULL;

There would be no results, because as commented, there is no way to compare something with NULL.

To circumvent the problem, you have two options:

1. Add to condition OR status_plataforma IS NULL, getting:

   SELECT id, preco, status_plataforma, concluida FROM compras WHERE site_cadastro_aluno_id = 43 AND (status_plataforma NOT IN (6,7) OR status_plataforma IS NULL);
   

2. Use the function coalesce for force a value if null - just be careful that this value is not in the given list:

   SELECT id, preco, status_plataforma, concluida FROM compras WHERE site_cadastro_aluno_id = 43 AND COALESCE(status_plataforma, 0) NOT IN (6,7);
   

In the second case, if the column is null, it will be evaluated as 0, and therefore the record will be selected since 0 does not belong to (6, 7).