MySQL 異なるデータ取得（SELECT）結果を結合して取得する方法について（UNION）

2024/01/26

似た様な複数の SELECT文 の結果を結合するために、連結するのが UNION です。
各 SELECT文 のカラム数及びデータ型は一致させておくべきです。

UNION について順を追って説明します。

UNIONの書き方
UNION DISTINCT と UNION ALL
UNION での ORDER BY と LIMIT
UNION での ORDER BY と LIMIT その例1
UNION での ORDER BY と LIMIT その例2

■UNIONの書き方

複数の SELECT文 の結果を結合する UNION の書式は以下の様です。

SELECT 「カラム一覧１」FROM 「テーブル１」...
UNION [ALL | DISTINCT] SELECT 「カラム一覧２」FROM 「テーブル２」...
[UNION [ALL | DISTINCT] SELECT 「カラム一覧Ｎ」FROM 「テーブルＮ」...]
-- UNION のみ記述 : 重複行は削除されます。(UNION DISTINCT)と同様
-- UNION ALL      : 重複行もそのまま表示されます。

UNION 結果セットのカラム名は、最初の SELECT ステートメントのカラム名が採用されます。
各 SELECT ステートメントの対応する位置にリストされているカラムは、同じデータ型に揃えた方が良いようです。

以下にテーブルをリテラルで書いた SQL を示します。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 'A' AS 'COLA', 'B' AS 'COLB';
+------+------+
| COLA | COLB |
+------+------+
| A    | B    |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
+------+------+
2 rows in set (0.00 sec)

1番目、2番目の SQL はテーブルを個別にデータ取得し、3番目の SQL で UNION を行っています。
確かに結合された結果セットが返されます。

■UNION DISTINCT と UNION ALL

結合の方法で、重複する行を削除する場合と、全てを取得する場合の例を示します。

以下の SQL は4個の SELECT文を UNION で連結しています。
3，4番目の SELECT文は、1，2番目の SELECT文と同じデータを返しますので重複分となり、結果セットには 1，2番目の SELECT文の結果のみが返される様になります。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB'
    -> UNION
    -> SELECT '1' AS 'COL11', '2' AS 'COL22'
    -> UNION
    -> SELECT 'A' AS 'COLAA', 'B' AS 'COLBB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
+------+------+
2 rows in set (0.00 sec)

以下の SQL は4個の SELECT文を UNION ALL で連結しています。
結果セットとしては　1，2，3，4番目の SELECT文のデータを順に返しています。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION ALL
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB'
    -> UNION ALL
    -> SELECT '1' AS 'COL11', '2' AS 'COL22'
    -> UNION ALL
    -> SELECT 'A' AS 'COLAA', 'B' AS 'COLBB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
| 1    | 2    |
| A    | B    |
+------+------+
4 rows in set (0.00 sec)

■UNION での ORDER BY と LIMIT

UNION で連結される SELECT文 の中で ORDER BY と LIMIT を使う場合は SELECT文 をカッコ「()」で囲みその中に配置します。以下の様な感じです。

(SELECT 「カラムリスト1」 FROM table1 WHERE 「条件1」 ORDER BY 「ソート指定1」 LIMIT 「行数1」)
UNION
(SELECT 「カラムリスト2」 FROM table2 WHERE 「条件2」 ORDER BY 「ソート指定2」 LIMIT 「行数2」);

ORDER BY と LIMIT を使う例を示すために、以下のページで作成したテーブルを使用します。

⇒ MySQL データ取得（SELECT）のWHRE句にサブクエリ条件で ANY ALL の使い方について（ANY、SOME、ALL）
以下の様な 商品テーブル（マスタ） と 売上数量テーブル を使用します。

MariaDB [test]> SELECT * FROM product;
+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | 鉛筆A    |    80 |
|  2 | 鉛筆B    |   100 |
|  3 | くれよん |   500 |
|  4 | 蛍光ペン |   120 |
|  5 | 消しゴム |   150 |
+----+----------+-------+
5 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM sales;
+----+--------+------------+--------+
| id | id_prd | salesdate  | volume |
+----+--------+------------+--------+
|  1 |      1 | 2024-01-04 |     10 |
|  2 |      1 | 2024-01-07 |     15 |
|  3 |      2 | 2024-01-07 |     20 |
|  4 |      3 | 2024-01-05 |      1 |
|  5 |      3 | 2024-01-10 |      2 |
|  6 |      3 | 2024-01-15 |      2 |
|  7 |      4 | 2024-01-05 |      5 |
|  8 |      4 | 2024-01-14 |      3 |
|  9 |      4 | 2024-01-20 |      1 |
| 10 |      5 | 2024-01-20 |      5 |
| 11 |      5 | 2024-01-22 |     10 |
+----+--------+------------+--------+
11 rows in set (0.01 sec)

商品テーブルと売上数量テーブルを連結しての商品名称入りの一覧を表示してみます。
今回のテストのための基本の SQL となります。

SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
LEFT JOIN product
ON sales.id_prd = product.id
ORDER BY sales.id;

この SQL の結果は以下の通りです。

+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  2 |      1 | 鉛筆A    | 2024-01-07 |     15 |
|  3 |      2 | 鉛筆B    | 2024-01-07 |     20 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  5 |      3 | くれよん | 2024-01-10 |      2 |
|  6 |      3 | くれよん | 2024-01-15 |      2 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
|  8 |      4 | 蛍光ペン | 2024-01-14 |      3 |
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
| 11 |      5 | 消しゴム | 2024-01-22 |     10 |
+----+--------+----------+------------+--------+
11 rows in set (0.00 sec)

■UNION での ORDER BY と LIMIT その例1

以下に連結される SELECT文の中に ORDER BY と LIMIT を持つテスト SQL を実行します。

最初の SELECT文は、上記の売上数量テーブルの売上日付に対して '2024-01-05' 以前の条件を付けて、出力件数を「2」にし、後の SELECT文は、売上数量テーブルの売上日付に対して '2024-01-20' 以降の条件を付けて、出力件数を「2」に設定しています。

MariaDB [test]> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate <= '2024-01-05'
    ->     ORDER BY sales.id
    ->     LIMIT 2
    -> )
    -> UNION
    -> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate >= '2024-01-20'
    ->     ORDER BY sales.id
    ->     LIMIT 2
    -> );
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
+----+--------+----------+------------+--------+
4 rows in set (0.00 sec)

最終的な結果セットは「4」行ですが、2個の SELECT文を個別に LIMIT 無しで実行してみます。

MariaDB [test]> SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    -> LEFT JOIN product
    -> ON sales.id_prd = product.id
    -> WHERE sales.salesdate <= '2024-01-05'
    -> ORDER BY sales.id;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
+----+--------+----------+------------+--------+
3 rows in set (0.00 sec)

MariaDB [test]> SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    -> LEFT JOIN product
    -> ON sales.id_prd = product.id
    -> WHERE sales.salesdate >= '2024-01-20'
    -> ORDER BY sales.id;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
| 11 |      5 | 消しゴム | 2024-01-22 |     10 |
+----+--------+----------+------------+--------+
3 rows in set (0.00 sec)

この結果から、UNION で連結した SQL では確かに個別の LIMIT が効いていることが分かります。

■UNION での ORDER BY と LIMIT その例2

UNION で連結された結果セットに対して ORDER BY と LIMIT を行う場合は、最後の文の後ろに ORDER BY と LIMIT を配置します。

上記の UNION の例の SQL で個別の SELECT文では ORDER BY と LIMIT を行わず、最後に配置してみます。

MariaDB [test]> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate <= '2024-01-05'
    -> )
    -> UNION
    -> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate >= '2024-01-20'
    -> )
    -> ORDER BY volume
    -> LIMIT 5;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
+----+--------+----------+------------+--------+
5 rows in set (0.05 sec)

売上数量の昇順で全ての UNION の結果データをソートしてから、先頭から5行の結果セットになっています。

MySQL
0CM

コメントを書く