今回は 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)
再帰クエリの中で使う
UNION のもう一つの重要な使い方として再帰クエリ (WITH RECURSIVE) の中での用法がある。
これは再帰的にクエリを実行して得られた内容を結合するのに UNION を使うということ。
再帰クエリについては以下に詳しく書いた。
ちなみに MySQL 5.7 には再帰クエリが実装されていないけど MariaDB 10.2 なら使える。
まとめ
今回は UNION を使う場面について見てみた。
- 作者: 加嵜長門,田宮直人,丸山弘詩
- 出版社/メーカー: マイナビ出版
- 発売日: 2017/03/27
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
