タイトルにある話はつまりこう
ここに都市名を入れたテーブルがあったとします。
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句で使いたい場合にはこれが出てくるだろうし、なんか納得する説明が欲しいんだけど誰か教えてチョモランマ。
と言ったら、神様仏様、いや、『兄貴ィ』降臨です!
- const OR NULLは
- constが真の時に短絡評価されて真
- constが偽の時に "偽とNULLの和集合" と評価されて不定(= NULL)
真 IS NULL
は偽偽 IS NULL
は偽NULL IS NULL
は真
おふざけが入りましたが、本当にスッキリしました。 ありがとうございました!