MySQLのUNION構文とかで試行錯誤したメモ

掲示板（でもなんでもいいが）を作るにあたり、たとえば以下のようにテーブルをくっつけてから記事を取得する。

mysql> (SELECT id, user, message, create_date FROM BBS_MESSAGE)
    -> UNION
    -> (SELECT id, user, message, create_date FROM BBS_REPLY)
    -> ORDER BY create_date DESC
    -> LIMIT 0, 5
    -> ;

この場合、「記事数」を取得するにはどうすればいいか？

最初に試したのが以下のSQL。

mysql> SELECT count(*) FROM (
    -> (SELECT id FROM BBS_MESSAGE)
    -> UNION
    -> (SELECT id FROM BBS_REPLY)
    -> );

これは失敗する。

ERROR 1248 (42000): Every derived table must have its own alias

FROMのあとの副問い合わせがUNION構文なときには、固有の名前を付けてあげないと駄目らしい。

mysql> SELECT count(*) FROM (
    -> (SELECT id FROM BBS_MESSAGE)
    -> UNION
    -> (SELECT id FROM BBS_REPLY)
    -> ) as UNI;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.03 sec)

とまあ、エラーは出ないところまで来た。

でも、

mysql> SELECT count(*) FROM BBS_MESSAGE;
+----------+
| count(*) |
+----------+
|       23 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM BBS_REPLY;
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

数合ってないよね？

いやまあ、カラクリは単純で、UNION構文は二つ（以上）のSELECTの結果をくっつける際、重複しているものは省略してしまうようだ。前回リファレンスにリンクを張ったんだからちゃんと読んどけと言う話だった……。　なのでidにいっぱい同じ値のものがあると数が合わない。

この場合、

mysql> SELECT count(*) FROM (
    -> (SELECT id FROM BBS_MESSAGE)
    -> UNION ALL
    -> (SELECT id FROM BBS_REPLY)
    -> ) as UNI;
+----------+
| count(*) |
+----------+
|       39 |
+----------+
1 row in set (0.00 sec)

「UNION ALL」で解決する。めでたしめでたし。

とまあ、ここまで書いといて、「create_dateが両方のテーブルについて一意なんだからそっちを取り出してcount(*)すればよくね？」　って思った。実は一意とは限らないんだけどね。記事をINSERTするときに

INSERT INTO BBS_MESSAGE
    ( ... , create_date )
    VALUES
    ( ... , now() )

としているため。秒まで同時に書き込んだらかぶるはずなのだが、滅多にそんなことなくね？（そもそも order byの条件にしてしまっている……）　という話。まあUNION ALLのほうがいいでしょう。