"NOT IN" behavior affecting column display with NULL value

Navigation
1

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

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

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

That is, I do not have 6 nor 7 in this column, but only NULL and, theoretically, the result should be returned.

My Tests:

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 you can see in the previous query, in fact status_plataforma is neither 6 nor 7 , but NULL .

Why did NOT IN have this behavior?

    
asked by anonymous 20.08.2018 / 14:34

1 answer

4

As discussed in Why NULL values are not selected? , NULL is not a value, then 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 work around the problem , you have two options:

  • Add condition OR status_plataforma IS NULL , where: 

    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);

  • Use the function coalesce to force a value if it is null - just be careful that this value is not in the indicated 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).

        
    20.08.2018 / 14:55
    Error Entity Framework | not handled in user code Tags generated dynamically with ajax are not seen by jquery