MySQLにおけるNULLと演算の結果

@tkyk04さん のブログ記事、 COUNT に「OR NULL」入れるの、正しい結果返すのわかったけど、心がザワザワしてたけど、今はスッキリした。(スゲーッ爽やかな気分だぜ) - 41から始めました を読んでふとメモ。

---

NULLの四則演算はわかりやすいですよね。

mysql80 21> SELECT 1 + NULL, 1 - NULL, 1 * NULL, 1 / NULL;
+----------+----------+----------+----------+
| 1 + NULL | 1 - NULL | 1 * NULL | 1 / NULL |
+----------+----------+----------+----------+
|     NULL |     NULL |     NULL |     NULL |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

NULLに対する四則演算はNULL。
「謎の数字に1を足したら何になる？」「(´・ω・`)知らんがな」

論理演算は知ってる人と知らない人に分かれそう。

mysql80 21> SELECT TRUE AND NULL, TRUE OR NULL, FALSE AND NULL, FALSE OR NULL;
+---------------+--------------+----------------+---------------+
| TRUE AND NULL | TRUE OR NULL | FALSE AND NULL | FALSE OR NULL |
+---------------+--------------+----------------+---------------+
|          NULL |            1 |              0 |          NULL |
+---------------+--------------+----------------+---------------+
1 row in set (0.00 sec)

MySQLの整数型において0以外は真、0は偽で、 TRUE は1のシノニム、 FALSE は0のシノニムです。

話はちょっと逸れますが、 TRUE は1のシノニムなので↓のようなことも可能（？）です。 = TRUE と等価比較すると値が真なのか偽なのかは判定できません。

mysql80 21> SELECT TRUE + TRUE /* 1 + 1 */, TRUE + TRUE = TRUE /* 1 + 1 = 1 */;
+-------------+--------------------+
| TRUE + TRUE | TRUE + TRUE = TRUE |
+-------------+--------------------+
|           2 |                  0 |
+-------------+--------------------+
1 row in set (0.00 sec)

真偽値を判定するには IS演算子 を使う必要があります。

mysql80 21> SELECT (TRUE + TRUE) IS TRUE, (TRUE + TRUE = TRUE) IS TRUE;
+-----------------------+------------------------------+
| (TRUE + TRUE) IS TRUE | (TRUE + TRUE = TRUE) IS TRUE |
+-----------------------+------------------------------+
|                     1 |                            0 |
+-----------------------+------------------------------+
1 row in set (0.00 sec)

単純な話、0ならFALSE(= 0)、それ以外ならTRUE(= 1)を返します。NULLセーフです。 IS演算子(またはIS NOT演算子)の右辺には TRUE, FALSE, UNKNOWNしか取れません。
IS NULL は別の演算子扱いでマニュアルも別れています。これ豆な。

で、戻ってきますが論理演算はこんな感じです（さっきと同じ出力）

mysql80 21> SELECT TRUE AND NULL, TRUE OR NULL, FALSE AND NULL, FALSE OR NULL;
+---------------+--------------+----------------+---------------+
| TRUE AND NULL | TRUE OR NULL | FALSE AND NULL | FALSE OR NULL |
+---------------+--------------+----------------+---------------+
|          NULL |            1 |              0 |          NULL |
+---------------+--------------+----------------+---------------+
1 row in set (0.00 sec)

AND演算子もOR演算子も左辺と右辺を交換しても結果が変わらない演算子なので、順番関わらずこうなります。
この順番で書くと、「短絡評価っぽいでしょ？」と説明しやすいのでよくそういうのですが、本当は短絡評価しているわけではなく、↓のような感じです。

• AND の場合、
  • 片方が偽なら式全体として偽が確定するので、偽 AND NULLは偽
  • 片方が真なら式全体の真偽はもう片方の値に依存するので、真 AND NULLはNULL
• OR の場合、
  • 片方が偽なら式全体の真偽はもう片方の値に依存するので、偽 OR NULLはNULL
  • 片方が真なら式全体としての真が確定するので、真 OR NULLは真

十分短絡評価っぽかった。
プログラミングの短絡評価と違うのは「MySQLは飽くまで「ANDもORも両辺を交換可能」と見た上で」こうなっているということ。

「この論理演算がパッと思いつかないならNOT NULLしといた方が踏むリスクはなくなるよ」というユルいNOT NULL推奨のPoCとしてご利用いただければと思います。

---

ちなみにPostgreSQL 9.1.10。
(PostgreSQLのTRUE, FALSEはシノニムではなくBool型なので、足したり引いたりはできません)

postgres=# SELECT TRUE AND NULL, TRUE OR NULL, FALSE AND NULL, FALSE OR NULL;
 ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
 [NULL]   | t        | f        | [NULL]
(1 row)

Oracle 11.2.0.4.0。
どうやらAND演算子がselect_listの中に入るのを許してくれない模様。

SQL> SELECT TRUE AND NULL, TRUE OR NULL, FALSE AND NULL, FALSE OR NULL FROM dual;
SELECT TRUE AND NULL, TRUE OR NULL, FALSE AND NULL, FALSE OR NULL FROM dual
            *
行1でエラーが発生しました。:
ORA-00923: FROMキーワードが指定の位置にありません。

​