Query is not updating null values

Question

I’m analyzing the data from a student database, the data is entered by the users themselves, and I can’t change the format of the tables and the database itself, just modify the values of the records to standardize some fields.

In the field of nationality, there are some wrong Brazilian records, written as brazeleiro or brasilero, etc. The records of other nationalities I did not want to modify, so I made the following query, this list in the query are all other records outside Brazilians:

UPDATE base
   SET nacionalidade = 'Brasileira'
   WHERE nacionalidade not in ('Americana', 'asdf', 'VENEZUELA', 'VENEZUELANO - BRASILEIRO');

The field still had some null values. These fields I didn’t want to touch either, but I ended up running the update, and it turns out that it didn’t update the null fields. My question is just wondering why he didn’t update?

Answer 1

The reason is that using comparison operators with NULL does not behave as expected.

This can be checked as follows:

WHERE nacionalidade NOT IN ('Americana', 'asdf', 'VENEZUELA', 'VENEZUELANO - BRASILEIRO');

can be translated to:

WHERE NOT ( nacionalidade = 'Americana' OR nacionalidade = 'asdf' OR (...))

which, after removing the NOT, is equivalent to:

WHERE nacionalidade != 'Americana' AND nacionalidade != 'asdf' AND (...))

In WHERE NOT ( nacionalidade = 'Americana' OR nacionalidade = 'asdf' OR (...)), if the comparison with NULL returns FALSE(expected), the expression would be true.
However we know that no field has been changed. It is as if, in this case, the comparison with NULL returns TRUE.

In WHERE nacionalidade != 'Americana' AND nacionalidade != 'asdf' AND (...)) the expected result is that the comparison returns TRUE and the expression would be true. However we know that no field has been changed. It is as if, in this case, the comparison with NULL returns FALSE.

So how come, in two equivalent expressions, the comparison behaves differently?

In reality the result of a comparison with NULL is not false or true, nor greater or lesser, it is UNKNOWN or NULL, depending on the implementation.

NULL indicates absence of a value, unknown value, nothing.
You can’t compare something that exists to something that doesn’t exist or is unknown.
The result of using any comparison operator with NULL is always UNKNOWN and result is ignored.

Hence the existence of the operator IS NULL.

Answer 2

It seems to me that this operator does not consider null values. To work, its UPDATE it should be like this:

UPDATE base
   SET nacionalidade = 'Brasileira'
   WHERE (nacionalidade not in ('Americana', 'asdf', 'VENEZUELA', 'VENEZUELANO - BRASILEIRO') or nacionalidade IS NULL)

Here is a post where the same problem is commented.