Why is 'WHERE NOT' with NULL-rows not working?-CodePudding

Maybe I'm just missing something. Since the problem is too unspecific to google it / search for it, I am now looking for help here. Tested in MySQL

My Question: Why doesn't this SELECT output [ id=2 ]?

SELECT `id` FROM `testtable1` WHERE NOT (`key_boolean` = 1);

Table Definition and Test-Data:

CREATE TABLE IF NOT EXISTS `testtable1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key_boolean` tinyint NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `testtable1` (`key_boolean`) VALUES (1);
INSERT INTO `testtable1` () VALUES ();

In the table is now stored: isn't it? [ id=1, key_boolean=1 ], [ id=2, key_boolean=NULL ]

CodePudding user response：

The expression key_boolean = 1 evaluates to NULL when key_boolean is NULL. So you intermediately have NOT NULL for NOT (key_boolean = 1). NOT NULL, in turn, also evaluates to NULL. And as NULL isn't true (yet not false either -- in SQL a ternary logic applies), the row is not selected.

With the exception of a few, all comparison operations, like =, evaluate to NULL, if one of their operands is NULL. One notable exception in MySQL, in this case, is the NULL-safe equal <=>, which behaves like you might want it.

You can select the (sub) expressions from your test table and see their results for yourself.

SELECT *,
       key_boolean = 1,
       NOT (key_boolean = 1),
       key_boolean <=> 1,
       NOT (key_boolean <=> 1)
       FROM testtable1;

db<>fiddle

CodePudding user response：

NULL conceptually means indeterminate. So NOT NULL is also NULL, and false. You can test CASE WHEN key_boolean=1 THEN 0 ELSE 1 END if you want to include NULL.