SQL: UNION を使ってテーブルを縦に連結する

今回は SQL の UNION を使ってみる。

試した環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

典型的な使い方

例えばスキーマ設計において、パフォーマンスの向上などを目的として同じカラムを持ったテーブルが複数あったりすることがある。特定の月のデータを入れるテーブルとか、あるいは毎月新しいテーブルを作っていくとか、そんな感じ。

次のサンプルでは同じカラムを持ったテーブルが日付で分かれている。

> CREATE TABLE purchases_20170627 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

> CREATE TABLE purchases_20170628 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

上記のテーブルに、それぞれサンプルのレコードを追加しておこう。

> INSERT INTO
    ->   purchases_20170627
    -> VALUES
    ->   (1, 'Alice'),
    ->   (2, 'Bob'),
    ->   (3, 'Carol');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

> INSERT INTO
    ->   purchases_20170628
    -> VALUES
    ->   (4, 'Alice'),
    ->   (5, 'Bob');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

さて、上記のようなテーブルを一つのクエリで処理したいとする。そんなときこそ今回扱う UNION の出番といえる。 UNION では複数の SELECT 文を繋げて扱うことができる。もちろん同じカラムという前提はあるけど。

次のクエリでは先ほどの二つのテーブルの内容をまとめて取得している。

> SELECT
    ->   '20170627' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170627
    -> UNION ALL
    -> SELECT
    ->   '20170628' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170628;
+----------+-------------+---------+
| date     | purchase_id | user_id |
+----------+-------------+---------+
| 20170627 |           1 | Alice   |
| 20170627 |           2 | Bob     |
| 20170627 |           3 | Carol   |
| 20170628 |           4 | Alice   |
| 20170628 |           5 | Bob     |
+----------+-------------+---------+
5 rows in set (0.00 sec)

同じカラムを持った内容をどんどん縦に繋げていくイメージ。

擬似的なテーブルを作るのに使う

続いては UNION を使って擬似的なテーブルを作るやり方について。例えば SELECT 文を UNION で繋いでいけば CREATE TABLE ... しなくても擬似的なテーブルが用意できる。

次のクエリでは WITH と共に使うことで擬似的に作ったテーブルの内容を表示している。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           2 | L2SW        |
|           3 | L3SW        |
+-------------+-------------+
3 rows in set (0.00 sec)

ちなみに、これまでは UNION ALL を使っていたけど、重複する内容を省きたいときは UNION DISTINC を使う。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           3 | L3SW        |
+-------------+-------------+
2 rows in set (0.00 sec)

同じ内容を持ったレコードが削除されている。

UNION で作った擬似的なテーブルも CREATE TABLE ... で作ったテーブルと同じように扱うことができる。例えば、次のように擬似的なテーブルと関連するようなテーブルを作っておく。

> CREATE TABLE devices(
    ->   id INTEGER,
    ->   type INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

> INSERT INTO
    ->   devices
    -> VALUES
    ->   (1, 1),
    ->   (2, 1),
    ->   (3, 2),
    ->   (4, 3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

そして両者を JOIN してみよう。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   devices.id,
    ->   device_types.device_name AS device_type
    -> FROM devices
    -> JOIN device_types
    -> ON devices.type = device_types.device_type;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.01 sec)

ちゃんと上手く JOIN できた。

まあ上記くらいの内容なら擬似的なテーブルを作るよりも CASE を使った方が楽ちんかな。

> SELECT
    ->   devices.id,
    ->   CASE
    ->     WHEN devices.type = 1 THEN 'LB'
    ->     WHEN devices.type = 2 THEN 'L2SW'
    ->     WHEN devices.type = 3 THEN 'L3SW'
    ->   END AS device_type
    -> FROM devices;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.00 sec)