41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

COUNT に「OR NULL」入れるの、正しい結果返すのわかったけど、心がザワザワしてたけど、今はスッキリした。(スゲーッ爽やかな気分だぜ)

f:id:next4us-ti:20191111231437j:plain

タイトルにある話はつまりこう

ここに都市名を入れたテーブルがあったとします。

mysql root@127.0.0.1:test> select * from city limit 10;
+-----------------+-------------------+-----------+--------------+------------------------+
| prefecture_id   | prefecture_name   | city_id   | city_name    | city_kana              |
|-----------------+-------------------+-----------+--------------+------------------------|
| 1               | 北海道            | 0         | <null>       | <null>                 |
| 1               | 北海道            | 100       | 札幌市       | サッポロシ             |
| 1               | 北海道            | 101       | 札幌市中央区 | サッポロシチュウオウク |
| 1               | 北海道            | 102       | 札幌市北区   | サッポロシキタク       |
| 1               | 北海道            | 103       | 札幌市東区   | サッポロシヒガシク     |
| 1               | 北海道            | 104       | 札幌市白石区 | サッポロシシロイシク   |
| 1               | 北海道            | 105       | 札幌市豊平区 | サッポロシトヨヒラク   |
| 1               | 北海道            | 106       | 札幌市南区   | サッポロシミナミク     |
| 1               | 北海道            | 107       | 札幌市西区   | サッポロシニシク       |
| 1               | 北海道            | 108       | 札幌市厚別区 | サッポロシアツベツク   |
+-----------------+-------------------+-----------+--------------+------------------------+
10 rows in set

実際には47都道府県分入っています。 city_idが0のものが都道府県を表しているレコードです。 (あ、このテーブル正規化されてなくてイケてないというツッコミはご勘弁をw あえてイメージしやすいようにしてるので)

定義はこちらです。

mysql root@127.0.0.1:test> desc city;
+-----------------+--------------+--------+-------+-----------+---------+
| Field           | Type         | Null   | Key   | Default   | Extra   |
|-----------------+--------------+--------+-------+-----------+---------|
| prefecture_id   | tinyint(2)   | NO     | PRI   | <null>    |         |
| prefecture_name | varchar(10)  | YES    |       | <null>    |         |
| city_id         | smallint(3)  | NO     | PRI   | <null>    |         |
| city_name       | varchar(600) | YES    |       | <null>    |         |
| city_kana       | varchar(216) | YES    |       | <null>    |         |
+-----------------+--------------+--------+-------+-----------+---------+
5 rows in set

件数は市区町村名まで入ってるレコードと47都道府県の分で1987件あるとします。

mysql root@127.0.0.1:test> select count(1) from city;
+------------+
| count(1)   |
|------------|
| 1987       |
+------------+
1 row in set

mysql root@127.0.0.1:test> select count(1) from city where city_name is null;
+------------+
| count(1)   |
|------------|
| 47         |
+------------+
1 row in set

mysql root@127.0.0.1:test> select * from city where city_id = 0;
+-----------------+-------------------+-----------+-------------+-------------+
| prefecture_id   | prefecture_name   | city_id   | city_name   | city_kana   |
|-----------------+-------------------+-----------+-------------+-------------|
| 1               | 北海道            | 0         | <null>      | <null>      |
| 2               | 青森県            | 0         | <null>      | <null>      |
| 3               | 岩手県            | 0         | <null>      | <null>      |
| 4               | 宮城県            | 0         | <null>      | <null>      |
| 5               | 秋田県            | 0         | <null>      | <null>      |
| 6               | 山形県            | 0         | <null>      | <null>      |
| 7               | 福島県            | 0         | <null>      | <null>      |
| 8               | 茨城県            | 0         | <null>      | <null>      |
| 9               | 栃木県            | 0         | <null>      | <null>      |
| 10              | 群馬県            | 0         | <null>      | <null>      |
| 11              | 埼玉県            | 0         | <null>      | <null>      |
| 12              | 千葉県            | 0         | <null>      | <null>      |
| 13              | 東京都            | 0         | <null>      | <null>      |
| 14              | 神奈川県          | 0         | <null>      | <null>      |
| 15              | 新潟県            | 0         | <null>      | <null>      |
| 16              | 富山県            | 0         | <null>      | <null>      |
| 17              | 石川県            | 0         | <null>      | <null>      |
| 18              | 福井県            | 0         | <null>      | <null>      |
| 19              | 山梨県            | 0         | <null>      | <null>      |
| 20              | 長野県            | 0         | <null>      | <null>      |
| 21              | 岐阜県            | 0         | <null>      | <null>      |
| 22              | 静岡県            | 0         | <null>      | <null>      |
| 23              | 愛知県            | 0         | <null>      | <null>      |
| 24              | 三重県            | 0         | <null>      | <null>      |
| 25              | 滋賀県            | 0         | <null>      | <null>      |
| 26              | 京都府            | 0         | <null>      | <null>      |
| 27              | 大阪府            | 0         | <null>      | <null>      |
| 28              | 兵庫県            | 0         | <null>      | <null>      |
| 29              | 奈良県            | 0         | <null>      | <null>      |
| 30              | 和歌山県          | 0         | <null>      | <null>      |
| 31              | 鳥取県            | 0         | <null>      | <null>      |
| 32              | 島根県            | 0         | <null>      | <null>      |
| 33              | 岡山県            | 0         | <null>      | <null>      |
| 34              | 広島県            | 0         | <null>      | <null>      |
| 35              | 山口県            | 0         | <null>      | <null>      |
| 36              | 徳島県            | 0         | <null>      | <null>      |
| 37              | 香川県            | 0         | <null>      | <null>      |
| 38              | 愛媛県            | 0         | <null>      | <null>      |
| 39              | 高知県            | 0         | <null>      | <null>      |
| 40              | 福岡県            | 0         | <null>      | <null>      |
| 41              | 佐賀県            | 0         | <null>      | <null>      |
| 42              | 長崎県            | 0         | <null>      | <null>      |
| 43              | 熊本県            | 0         | <null>      | <null>      |
| 44              | 大分県            | 0         | <null>      | <null>      |
| 45              | 宮崎県            | 0         | <null>      | <null>      |
| 46              | 鹿児島県          | 0         | <null>      | <null>      |
| 47              | 沖縄県            | 0         | <null>      | <null>      |
+-----------------+-------------------+-----------+-------------+-------------+
47 rows in set

さて問題

select count(1) from city where city_name is null;を実行した場合にかえる数値は?

当然、47です。

mysql root@127.0.0.1:test> select count(1) from city where city_name is null;
+------------+
| count(1)   |
|------------|
| 47         |
+------------+
1 row in set

では、select count(city_name is null) from city; を実行した場合に返る数値は?

この場合、全件がかえります。

mysql root@127.0.0.1:test> select count(city_name is null) from city;
+----------------------------+
| count(city_name is null)   |
|----------------------------|
| 1987                       |
+----------------------------+
1 row in set

ここでの city_name is null はtrueの意味の1とfalseの意味の0を返してます。

mysql root@127.0.0.1:test> select *,city_name is null from city limit 10;
+-----------------+-------------------+-----------+--------------+------------------------+---------------------+
| prefecture_id   | prefecture_name   | city_id   | city_name    | city_kana              | city_name is null   |
|-----------------+-------------------+-----------+--------------+------------------------+---------------------|
| 1               | 北海道            | 0         | <null>       | <null>                 | 1                   |
| 1               | 北海道            | 100       | 札幌市       | サッポロシ             | 0                   |
| 1               | 北海道            | 101       | 札幌市中央区 | サッポロシチュウオウク | 0                   |
| 1               | 北海道            | 102       | 札幌市北区   | サッポロシキタク       | 0                   |
| 1               | 北海道            | 103       | 札幌市東区   | サッポロシヒガシク     | 0                   |
| 1               | 北海道            | 104       | 札幌市白石区 | サッポロシシロイシク   | 0                   |
| 1               | 北海道            | 105       | 札幌市豊平区 | サッポロシトヨヒラク   | 0                   |
| 1               | 北海道            | 106       | 札幌市南区   | サッポロシミナミク     | 0                   |
| 1               | 北海道            | 107       | 札幌市西区   | サッポロシニシク       | 0                   |
| 1               | 北海道            | 108       | 札幌市厚別区 | サッポロシアツベツク   | 0                   |
+-----------------+-------------------+-----------+--------------+------------------------+---------------------+
10 rows in set

where 条件と同じ結果をSELECT 句のCOUNT内で表現するにはどうすれば良いでしょう? OR NULLを足します

mysql root@127.0.0.1:test> select count(city_name is null or null) from city;
+------------------------------------+
| count(city_name is null or null)   |
|------------------------------------|
| 47                                 |
+------------------------------------+
1 row in set

ちなみに、MySQLでCOUNT内のカラムにNULLが含まれている場合は、NULLのカラムはカウント対象から外れます。 これはマニュアルにも載ってますし、Oracle等でも同じなので結構おなじみな話だと思います。

mysql root@127.0.0.1:test> select count(city_name) from city;
+--------------------+
| count(city_name)   |
|--------------------|
| 1940               |
+--------------------+
1 row in set

OR NULLを入れるとなぜ正しい結果がかえるのか?

  • count(city_name is null) -> TRUEもFALSEもかえす
  • count(city_name is null or null) -> 「OR NULL」を付けることで前の構文に当てはまればTRUE、それ以外はNULL -> NULLはカウント非対象 -> 正しい結果がかえる

何に心がザワつくのか?

  • 「OR NULL」付けたら条件として見るというのがイマイチ納得いかない。(じゃあ、IS NULLは何なの?)
  • 「OR NULL」付けたら0をNULLに置き換えてるの?

基本的にはWHERE句でやるべきことをCOUNTとかでやろうとするとこのように書くということで、本当は使わないほうがいいんだけど HAVING句で使いたい場合にはこれが出てくるだろうし、なんか納得する説明が欲しいんだけど誰か教えてチョモランマ。

と言ったら、神様仏様、いや、『兄貴ィ』降臨です!

gist.github.com

f:id:next4us-ti:20191111210742p:plain

  • const OR NULLは
    • constが真の時に短絡評価されて真
    • constが偽の時に "偽とNULLの和集合" と評価されて不定(= NULL)
  • 真 IS NULL は偽
  • 偽 IS NULL は偽
  • NULL IS NULL は真

f:id:next4us-ti:20191111231147j:plain

f:id:next4us-ti:20191111211150p:plain

おふざけが入りましたが、本当にスッキリしました。 ありがとうございました!