I tried to use the exclamation mark as a not operator in a query
SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2;
| !0 | !!0 | !!!0 | !1 | !!1 | !!!1 | !2 | !!2 | !!!2 |
|----|-----|------|----|-----|------|----|-----|------|
| 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
the results are surprising, but it's working as I thought it would if I add parentheses or spaces:
SELECT !0, !(!0), !(!(!0)), !1, !(!1), !(!(!1)), !2, !(!2), !(!(!2));
SELECT !0, ! !0, ! ! !0, !1, ! !1, ! ! !1, !2, ! !2, ! ! !2;
| !0 | !(!0) | !(!(!0)) | !1 | !(!1) | !(!(!1)) | !2 | !(!2) | !(!(!2)) |
|----|-------|----------|----|-------|----------|----|-------|----------|
| 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
EXPLAIN EXTENDED SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2;
SHOW WARNINGS;
when executing those 2 queries, I have the following message from SHOW WARNINGS
:
select (not(0)) AS `!0`,(not(0)) AS `!!0`,(0 <> 0) AS `!!!0`,(not(1)) AS `!1`,(not(1)) AS `!!1`,(1 <> 0) AS `!!!1`,(not(2)) AS `!2`,(not(2)) AS `!!2`,(2 <> 0) AS `!!!2`
Can anyone explain why mysql (and mariadb) comes to such a query?
<>
is the "not equal" operator. It's a binary operator that requires two values. That is something different than a the unaryNOT
operator on a "boolean" value.SELECT ! ! ! 0, ...;
works as expected.