Mysql - Clause problem WHERE `column` != NULL [BUG? ]

Question

I came across the following problem:

In my application I have a function that checks whether some value is already in use in another record, soon I must pass the id of the current record to delete it in query of this function. And this is where the problem happens. If I do this check before of there being a record, my clause would have the id != NULL, that by my logic, I should seek this value in all records, since they must have a defined ID!

All explained! Now in practice:

SELECT VERSION();
-- +-------------------------+
-- | VERSION()               |
-- +-------------------------+
-- | 5.7.21-0ubuntu0.17.10.1 |
-- +-------------------------+

SELECT @@GLOBAL.sql_mode;
-- +------------------------------------------------------------------------------------------------------------------------+
-- | @@GLOBAL.sql_mode                                                                                                      |
-- +------------------------------------------------------------------------------------------------------------------------+
-- | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
-- +------------------------------------------------------------------------------------------------------------------------+


CREATE DATABASE `teste`;

USE `teste`;

CREATE TABLE `tabteste` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `nome` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
    `cpf` varchar(14) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `cpf` (`cpf`)
) ENGINE=InnoDB;


INSERT INTO `tabteste` (`id`, `nome`, `cpf`) VALUES
    (1, 'FULANO', '677.215.110-03'),
    (2, 'SICRANO', NULL),
    (3, 'BELTRANO', '612.463.660-37'),
    (4, 'FOO', NULL),
    (5, 'BAR', '755.926.990-77'),
    (6, 'FUBÁ', '577.862.570-71');

SELECT COUNT(*) AS `quant` FROM `tabteste` WHERE (`cpf` = '577.862.570-71' AND `id` != 6);
-- RETORNA 0, POIS O ID 6 (FUBÁ), PASSADO COMO PARÂMETRO NA QUERY, USA ESSE CPF
-- +-------+
-- | quant |
-- +-------+
-- |     0 |
-- +-------+

SELECT COUNT(*) AS `quant` FROM `tabteste` WHERE (`cpf` = '577.862.570-71' AND `id` != 1234);
-- RETORNA 1, POIS O ID 6 (FUBÁ), DIFERENTE DO PARÂMETRO 1234 NA QUERY, JÁ ESTÁ USANDO ESSE CPF
-- +-------+
-- | quant |
-- +-------+
-- |     1 |
-- +-------+

SELECT COUNT(*) AS `quant` FROM `tabteste` WHERE (`cpf` = '577.862.570-71' AND `id` != NULL);
-- RETORNA 0 POR QUÊ? CONSIDERANDO QUE O ID 6 É DIFERENTE DE NULL!
-- +-------+
-- | quant |
-- +-------+
-- |     0 |
-- +-------+

At last query SELECT should return 1 also, since 6 is different from NULL !

Why does this happen?

I want to solve this without having to create a condition within the application to check if the id is NULL and write a query just for that, since apparently that would be a BUG of Mysql.

Answer 1

The NULL is a figure created to represent a value undefined or unknown, therefore it cannot be compared to anything (nor compared to null even). Either it is null, or it is not.

To take it otherwise, we have no way of knowing if this is true:

valor desconhecido = valor desconhecido

Not even that:

valor desconhecido <> valor desconhecido

because they could theoretically be equal or not.

To know if a field is null or not, if they use IS NULL and IS NOT NULL.

Correcting your query:

... WHERE (`cpf` = '577.862.570-71' AND `id` IS NOT NULL)
                                           --^^^^^^^^^^^