SQL の UNION!

サーバーを管理する上で避けて通れないのが RDB サーバー。なんだかんだで SQL とかいうやつにも親しまないといけません。僕も多少いじったことがあるので、SQL なんてちょろいもんよと有頂天になっていましたが…。ばかもーん!SQL は奥が深いんじゃ!ということを再認識しました。

下のような2つのテーブルがあるとします。

テーブル構成図

theme は議論のテーマ、response はテーマに対する発言、みたいなイメージです。

サンプルデータ (MySQL):

CREATE TABLE theme(
  id         INT PRIMARY KEY AUTO_INCREMENT,
  body       VARCHAR(255),
  created_at DATETIME
) ENGINE=InnoDB;

CREATE TABLE response(
  id         INT PRIMARY KEY AUTO_INCREMENT,
  theme_id   INT,
  body       VARCHAR(255),
  created_at DATETIME,
  FOREIGN KEY(theme_id) REFERENCES theme(id)
) ENGINE=InnoDB;


INSERT INTO theme(body, created_at)
VALUES(
  "陶淵明の作品について語りましょう",
  "2014-04-23 10:00:00"
);

INSERT INTO theme(body, created_at)
VALUES(
  "晩ご飯について語りましょう",
  "2014-04-21 10:00:00"
);

INSERT INTO response(theme_id, body, created_at)
VALUES(
  2,
  "カレーが食べたいです。",
  "2014-04-22 10:00:00"
);

INSERT INTO response(theme_id, body, created_at)
VALUES(
  2,
  "おすしが食べたいです。",
  "2014-04-22 11:00:00"
);

INSERT INTO response(theme_id, body, created_at)
VALUES(
  2,
  "うどんが食べたいです。",
  "2014-04-23 11:00:00"
);

さて、theme と response を無差別に日時順に並べたい。どうすればいいでしょう。

そもそもテーブルが違うんだし、そんなことできないんじゃないの〜なんて僕は思いました。が、「そういえば UNION なんていう使ったことのない機能があるぞ…」と思い試したところ、なんとうまくいくじゃないですか…。

(
  SELECT id theme_id, 0 response_id, body, created_at
  FROM theme
)
UNION
(
  SELECT theme_id, id, body, created_at
  FROM response
)
ORDER BY created_at;

結果:

*************************** 1. row ***************************
   theme_id: 2
response_id: 0
       body: 晩ご飯について語りましょう
 created_at: 2014-04-21 10:00:00
*************************** 2. row ***************************
   theme_id: 2
response_id: 1
       body: カレーが食べたいです。
 created_at: 2014-04-22 10:00:00
*************************** 3. row ***************************
   theme_id: 2
response_id: 2
       body: おすしが食べたいです。
 created_at: 2014-04-22 11:00:00
*************************** 4. row ***************************
   theme_id: 1
response_id: 0
       body: 陶淵明の作品について語りましょう
 created_at: 2014-04-23 10:00:00
*************************** 5. row ***************************
   theme_id: 2
response_id: 3
       body: うどんが食べたいです。
 created_at: 2014-04-23 11:00:00
5 rows in set (0.00 sec)

theme には response_id がないので 0 にしました。これでレコードが theme なのか response なのか見分けることができます。

うまくできたのは喜ばしいんですが、でも、できていいんでしょうか…。SELECT するカラムの数とそれぞれの型さえ合ってればテーブルががっちゃんこできるって、随分アグレッシブな気がするんですが…。

なお、ちょっとひねって「response がない theme は取り出す、ある場合は取り出さない」という時はこうなります:

(
  SELECT id theme_id, 0 response_id, body, created_at
  FROM theme
  WHERE id NOT IN (
    SELECT theme_id FROM response
  )
)
UNION
(
  SELECT theme_id, id, body, created_at
  FROM response
)
ORDER BY created_at;

結果です:

*************************** 1. row ***************************
   theme_id: 2
response_id: 1
       body: カレーが食べたいです。
 created_at: 2014-04-22 10:00:00
*************************** 2. row ***************************
   theme_id: 2
response_id: 2
       body: おすしが食べたいです。
 created_at: 2014-04-22 11:00:00
*************************** 3. row ***************************
   theme_id: 1
response_id: 0
       body: 陶淵明の作品について語りましょう
 created_at: 2014-04-23 10:00:00
*************************** 4. row ***************************
   theme_id: 2
response_id: 3
       body: うどんが食べたいです。
 created_at: 2014-04-23 11:00:00
4 rows in set (0.00 sec)

これは別になんてことない、普通のサブクエリです。やはり肝は UNION にあります。

theme の body をなくして、代わりに theme 作成時に response を1つ作るようにすれば UNION なんて使わずに済むんですが…。まあ後からどうこう言ってもしょうがないですね。

(コウヅ)

広告