MySQLのunionの使い方について、サンプルSQLを使ってまとめました。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。
サンプルデータベースのインストール方法は、下記を参考にしてください。
【関連記事】
▶MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的
MySQLのUNIONの構文
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.4 UNION 構文
複数のselect文の結果を1つの結果に統合するためのステートメントです。２つめ以降の結果は、１つめの結果と同じデータ型にする必要があります。データ型さえ合っていれば、異なるテーブルのselect結果でもunionが使用可能です。
ファイル出力を行うinto outfileは、最後のselect文にのみ使用可能。
SQL99（SQL3）に準拠したSQL標準のステートメントです。特にMySQL独自の拡張はないようです。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
select * from employees where emp_no < 10005 union select * from employees where emp_no between 10007 and 10010;
上記SQLは、employeesテーブルから、emp_no<10005の行と、emp_noが10007～10010の行をselectして結合するSQLです。実行結果はこうなります。
+--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 8 rows in set (0.00 sec)
MySQLのunionサンプルSQLコード
union allの前に、select文ごとにorder byを実行する方法
複数のselect文ごとにorder byでソートをするには、それぞれのselect文を括弧で囲み、limitを合わせて指定する必要があります。
mysql> (select * from employees where emp_no <= 10010 order by birth_date limit 10) UNION ALL ( select * from employees where emp_no between 10011 and 10020 order by first_name limit 10);
上記のSQLは、employees（雇用者テーブル）のうち、emp_noが10010以下の10件をbirth_date（誕生日）でソートし、emp_noが1011～10020の10件をfirst_name（姓名の名）でソートして結合します。
実行結果はこうなります。
+--------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-------------+--------+------------+ | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 | | 10017 | 1958-07-06 | Cristinel | Bouloucos | F | 1993-08-03 | | 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 | | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 | | 10018 | 1954-06-19 | Kazuhide | Peha | F | 1987-04-03 | | 10016 | 1961-05-02 | Kazuhito | Cappelletti | M | 1995-01-27 | | 10019 | 1953-01-23 | Lillian | Haddadi | M | 1999-04-30 | | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 | | 10020 | 1952-12-24 | Mayuko | Warwick | M | 1991-01-26 | | 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 | +--------+------------+------------+-------------+--------+------------+ 20 rows in set (0.00 sec)
limitなしでorder byを指定した場合は、ソート条件は無視されます。また、UNION ALLではなくUNIONを使用した場合も強制的にソートされるため、注意しましょう。
unionで結合したクエリをjoinする方法
mysql> select Q1.emp_no, first_name, last_name, dept_name from (select emp_no, first_name, last_name from employees where emp_no < 10010 union all select 10100, 'yamada', 'tarou') Q1 left join dept_emp on Q1.emp_no = dept_emp.emp_no left join departments on dept_emp.dept_no = departments.dept_no;
unionで結合したselect文を括弧で囲んでサブクエリにすることでjoin可能です。
上記のSQLは、emp_noが10010より小さいemployeesのレコードと、emp_no=10100、first_name=’yamada’、last_name=’tarou’というデータをunionで結合し、dept_emp（部署・雇用者紐付けテーブル）と、departments（部署テーブル）をjoinしています。
実行結果は以下のようになります。
+--------+------------+-----------+--------------------+ | emp_no | first_name | last_name | dept_name | +--------+------------+-----------+--------------------+ | 10001 | Georgi | Facello | Development | | 10002 | Bezalel | Simmel | Sales | | 10003 | Parto | Bamford | Production | | 10004 | Chirstian | Koblick | Production | | 10005 | Kyoichi | Maliniak | Human Resources | | 10006 | Anneke | Preusig | Development | | 10007 | Tzvetan | Zielinski | Research | | 10008 | Saniya | Kalloufi | Development | | 10009 | Sumant | Peac | Quality Management | | 10100 | yamada | tarou | Human Resources | +--------+------------+-----------+--------------------+ 10 rows in set (0.00 sec)
unionで仮想的な連番テーブルを生成する方法
mysql> SELECT @seq := 1 AS seq UNION SELECT @seq := @seq + 1 AS seq from employees LIMIT 15;
fromで指定しているemployeesは、テーブルの件数を擬似的なループのために利用しています。十分なデータ件数があるテーブルであれば何でも構いません。
実行するとこうなります。
+------+ | seq | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | +------+ 15 rows in set (0.23 sec)
ただし、件数の少ないdepartmentsテーブル（9件）をfromに指定するとこうなります。
+------+ | seq | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00 sec)
9回のループで、生成される連番は10までとなります。
まとめ
- UNIONは、select文の結果を結合できる
- UNIONは、結果を自動的にソートする。ソートしたくない場合は、UNION ALLを使う。
- UNION ALLでORDER BYを使う場合は、LIMITを指定する。指定しないと自動ソートされる。
- UNIONしたクエリをJOINするには、括弧で囲んでサブクエリ化する。
- UNIONで連番テーブルを生成する場合、fromで指定するテーブルには十分な件数のあるものを選ぶ。
