IS !value a shortcut for IS NOT NULL MySQL?

Question

When playing with MySQL I noticed that if I run

SELECT * FROM table WHERE !value;

returns the same thing as

SELECT * FROM table WHERE value IS NOT NULL;

The table is "employees" and the column is "MiddleInitial" (strings). So I get all employees who's MiddleInitial is not null.

Is this a proper shorthand or just a coincidence? I am wondering if this is a safe way to write? I cannot seem to find any information on this.

I was expecting

SELECT * FROM table WHERE !value;

to return all null values. Oddly enough

SELECT * FROM table WHERE value;

returns nothing.

Answer 1

No, it's not.

It might seem like it because it's doing type coercion to force whatever is in the column into a boolean value. NULL values will coerce to false when forced as a boolean predicate, and most non-null column values will coerce to true. But some column values (that are not null) will also coerce to false.

You can see examples here:

https://dbfiddle.uk/ABLUgLex

Notice the last example is missing the 0 row. Also notice the one before that does not include the null row, which leads me to suspect your server might have an option set for non-standard null handling.

Here's a few more samples:

https://dbfiddle.uk/BNxiujKt

Notice the treatment of the '1' row.

Answer 2

In SQL, NULL is not the same as false.

Negating NULL is not true, it's still NULL.

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+

mysql> select !(null);
+---------+
| !(null) |
+---------+
|    NULL |
+---------+

Think of NULL as the value "unknown." If some piece of information is unknown, how can its opposite be known? It can't — the opposite is also unknown, because we don't know what we started with.

When used in a WHERE clause condition, NULL acts more or less like false because neither are strictly true. Only rows where the conditions are true become part of the result set of the query.

There are other values that act like false in MySQL:

mysql> select 1 where '';
Empty set (0.01 sec)

mysql> select 1 where 0;
Empty set (0.01 sec)

MySQL is a bit nonstandard because the boolean values true and false are literally the same as the integer values 1 and 0 respectively (this is not the way booleans are implemented in most other brands of SQL database).

These values are not NULL, so they can be negated and you can treat their opposites as true.

mysql> select 1 where !0;
+---+
| 1 |
+---+
| 1 |
+---+

mysql> select 1 where !'';
+---+
| 1 |
+---+
| 1 |
+---+

As the comment above said, the ! operator is deprecated in MySQL 8.0. It's not standard SQL, and using it makes your code less clear than if you use more explicit language like IS NOT NULL or <>.