Why isn't 'WHERE NOT' with NULL-rows working?

Question

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:

id	key_boolean
1	1
2	NULL

---

Edit: Based on the answers I actually found some more similar questions on StackOverflow. This post fits well for MySQL: MYSQL syntax not evaluating not equal to in presence of NULL

Answer 1

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

Answer 2

Personally, I find the answers from @sticky-bit and @ysth very helpful, but I would like to share some more information about my research:

Conclusion: SQL follows the three-value logic (3VL). That means besides TRUE(1) and FALSE(0) there is also NULL. And that means that if one of the operands in a comparison is NULL, the result is also NULL. See also truth table on Wikipedia: https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_(3VL)

---

Example 1 without NOT:

SELECT id FROM testtable1 WHERE (key_boolean = 1);, so:

• [row 1] key_boolean = 1 --> 1 = 1 --> TRUE
• [row 2] key_boolean = 1 --> NULL = 1 --> NULL

-> so, row 1 is selected.

Example 2, just like example 1, only negated, so ​with NOT:

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

• [row 1] NOT (key_boolean) = 1 --> 1 = 1 --> TRUE
• [row 2] NOT (key_boolean) = 1 --> NULL = 1 --> NULL

-> empty result.

---

Solutions to the problem:

As @sticky-bit has already written, there is NULL-safe equal in MySQL, which in this example also achieves the desired result. <=> unfortunately only works in MySQL (and then also in MariaDB). I found a comparison of the different dialects at https://modern-sql.com/feature/is-distinct-from. @ysth's suggestion with "CASE WHEN" probably works in all dialects, but blows up select-terms.

In my specific case (no longer part of the actual question) I have now solved it with a COALESCE() - determined a default for NULL values, because in my case, i have to not only support mysql, see:

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

Further reading: If, like me, one has never heard of NULL-safe equal operator <=>, see: What is this operator <=> in MySQL?

Now I am by no means a database technician, but know NULL as undefined (from other programming languages). The obvious question to me is why at least SQL doesn't use the NULL-safe equal every time. See: Is there a reason not to use <=> (null safe equals operator) in mysql instead of =? and Is there a reason not to use <=> (null safe equals operator) in mysql instead of =? [each with a marked post].

Answer 3

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.