自分では「知っている」と思っていても、会話の中でふと自信がなくなることってありますよね。 自分の知識として「常識」になっているけど、気づけば10年以上も意識して試したことのないものとか。
表題のテーマが、今回の私のソレです。
ということで、COUNT(*) とか COUNT(カラム名) とかの書き方で動作がどう変わるのか改めて試した結果を整理してみた、という、初心者向きの内容です。
サンプルデータ
CREATE TABLE sample01 (id integer primary key, code char(2), name varchar(20)); INSERT INTO sample01 VALUES (1, '21', '名前1'); INSERT INTO sample01 VALUES (2, '22', '名前2'); INSERT INTO sample01 VALUES (3, null, '名前3'); INSERT INTO sample01 VALUES (4, '22', '名前4'); INSERT INTO sample01 VALUES (5, '22', '名前5'); INSERT INTO sample01 VALUES (6, '21', '名前6'); INSERT INTO sample01 VALUES (7, null, '名前7');
mysql> SELECT * FROM sample01; +----+------+---------+ | id | code | name | +----+------+---------+ | 1 | 21 | 名前1 | | 2 | 22 | 名前2 | | 3 | NULL | 名前3 | | 4 | 22 | 名前4 | | 5 | 22 | 名前5 | | 6 | 21 | 名前6 | | 7 | NULL | 名前7 | +----+------+---------+ 7 rows in set (0.01 sec)
COUNT(*)
COUNT(*) は、条件に該当する全行の数を返します。今回はWHEREを指定していないのでテーブル全件の7件が返ります。もちろん WHERE を指定すればそれに合致する行の数が返ります。
mysql> SELECT COUNT(*) FROM sample01; +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec)
COUNT(カラム名)
COUNT()の中にカラム名を指定した場合、そのカラムに有効な値がセットされているものの行数が返ります。もう少し具体的に言うと「NULLでないものの行数」が返ります。
mysql> SELECT COUNT(code) FROM sample01; +-------------+ | COUNT(code) | +-------------+ | 5 | +-------------+ 1 row in set (0.00 sec)
このことは、もちろんちゃんと知っていればこのような動作であることを見ただけで理解できるのですが、一般には直感的だとは私は思えません。
NULLを除外した件数を取得したい場合は、私ならまず以下のように条件として明示することを勧めたいところです。
（他の取得カラムや抽出条件との関係で、意図して COUNT(カラム名)を使う場合もあります）
mysql> SELECT COUNT(*) FROM sample01 WHERE code IS NOT NULL; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
COUNT(DISTINCT カラム名)
COUNT()の中に DISTINCT カラム名 を指定した場合は、指定されたカラムのユニーク項目数を返します。NULLは含まれません。
以下のとおり「2」（codeの値が 21 と 22の2種類あるので）と返ってくることが確認できました。
mysql> SELECT COUNT(DISTINCT code) FROM sample01; +----------------------+ | COUNT(distinct code) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec)
余談
実は「*」には "その行がある"、という程度の意味しかなくて（私の理解が間違っていたらご指摘ください＞ガチ勢のみまさま）、つまり行に相当する何かあればカウント対象になるわけです。ですから、固定文字列だったり適当な数字だったりを指定しても COUNT(*) と同様の動作をします。誰も何の得もしないので、よい子はまねをしないように。
mysql> SELECT COUNT('test') FROM sample01; +---------------+ | COUNT('test') | +---------------+ | 7 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(3.141592) FROM sample01; +-----------------+ | COUNT(3.141592) | +-----------------+ | 7 | +-----------------+ 1 row in set (0.00 sec)
.
.
追記（2022/07/20 00:15)
さっそく yoku0825さんからツッコミをいただきました。
EXPLAIN(5.6とそれ以前はEXTENDED)からのSHOW WARNINGSで見ると、COUNT(*)はCOUNT(1)にオプティマイズされてるっぽいですよね
— yoku0825 (@yoku0825) 2022年7月19日
COUNT()関数の中身の書き方による動作の違い - sakaikの日々雑感～(T)編 https://t.co/3IgRoQsq68
EXPLAIN(5.6とそれ以前はEXTENDED)からのSHOW WARNINGSで見ると、COUNT(*)はCOUNT(1)にオプティマイズされてるっぽいですよね
確かに、EXPLAIN 取ってみると、warning が1件出ている！
(関係ないけど Extra 列に最近マイブームのUsingが出ています。本当に関係ない)
mysql> explain SELECT COUNT(*) FROM sample01; +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | sample01 | NULL | index | NULL | PRIMARY | 4 | NULL | 7 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
warningの中身を見てみると・・・・・・
mysql> show warnings; +-------+------+---------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`sample01` | +-------+------+---------------------------------------------------------------------+ 1 row in set (0.00 sec)
COUNT(*) が COUNT(0)になってるー！！！！
一方の COUNT('TEST') (よい子はまねしちゃいけないやつ)は、、、、、
mysql> desc SELECT COUNT('TEST') FROM sample01; +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | sample01 | NULL | index | NULL | PRIMARY | 4 | NULL | 7 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select count('TEST') AS `COUNT('TEST')` from `test`.`sample01` | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
・・・そのまま(笑)。
.
.
追記(2022/07/20 09:00)
とみたまさひろさんから、COUNT()の引数は「式」であるとの指摘をいただきました。なるほど！
COUNT(カラム名) は実は COUNT(式) の特殊な形式なので、カラムではなく式を書いて「その式の評価結果が NULL でないもの」を数えることもできるんですよね。
— とみたまさひろ🍣🍺 (@tmtms) 2022年7月19日
なんか面白いことできないかと試みたのですが、以下、code列の値が21であるものの件数を得る試みですが、マッチしない場合の結果は NULL ではなく false（というか0） になるだけなので、意図失敗。
mysql> SELECT COUNT(code=21) FROM sample01; +----------------+ | COUNT(code=21) | +----------------+ | 5 | +----------------+ 1 row in set (0.01 sec)
やりたければ、こうなんだろうけど、ここまでやらなくてもねぇ(笑)。
mysql> SELECT COUNT(CASE code WHEN 21 THEN true ELSE null END) FROM sample01; +--------------------------------------------------+ | COUNT(CASE code WHEN 21 THEN true ELSE null END) | +--------------------------------------------------+ | 2 | +--------------------------------------------------+ 1 row in set (0.00 sec)
