MySQL UNION それぞれのSELECT文で取ってきたデータを、それぞれでソート(ORDER BY)してくっつけたい。

MySQLで別々の条件(SELECT文)で取ってきたデータをそれぞれでソートして結合したい。
という事で、2回SQLを発行して取ってきたデータをマージしてしまえば実現できることなのですが、SQL1回で実現できないかと思い調べていて色々とハマったのでメモ。

テーブル
テーブル名: test_table

+----+------+------+
| id | num  | text |
+----+------+------+
|  1 |   10 | b    |
|  2 |    9 | c    |
|  3 |    8 | a    |
|  4 |    7 | e    |
|  5 |    6 | f    |
|  6 |    5 | d    |
|  7 |    4 | i    |
|  8 |    3 | g    |
|  9 |    2 | h    |
| 10 |    1 | j    |
+----+------+------+

UNION (SQL)

複数のSELECT文をまとめることが出来る。
結合するSELECT文で取ってくるカラム数が同じである必要がある。(カラムのデータ型も同じである必要がありそう？)

UNION - 重複を排除するためにソートする
UNION ALL - ソートしない

重複しない条件で取ってくる場合はUNION ALLを使う方が高速なので良さそうです。

SELECT * FROM test_table WHERE num in (1,2,3)
UNION ALL
SELECT * FROM test_table WHERE num in (4,5,6);

👇 結果

+----+------+------+
| id | num  | text |
+----+------+------+
|  8 |    3 | g    |
|  9 |    2 | h    |
| 10 |    1 | j    |
|  5 |    6 | f    |
|  6 |    5 | d    |
|  7 |    4 | i    |
+----+------+------+

1つめのSELECTで取得されるデータの後に
2つめのSELECTで取得されるデータがくっつき
それぞれidの順になりました。

text順でソートしたい

データを一括でソートする場合は最後にORDER BY <条件>をつければOK

SELECT * FROM test_table WHERE num in (1,2,3)
UNION ALL
SELECT * FROM test_table WHERE num in (4,5,6)
ORDER BY text;

👇結果

+----+------+------+
| id | num  | text |
+----+------+------+
|  6 |    5 | d    |
|  5 |    6 | f    |
|  8 |    3 | g    |
|  9 |    2 | h    |
|  7 |    4 | i    |
| 10 |    1 | j    |
+----+------+------+

ソート条件は同じで、1つめのSELECT文で取れるデータをソートした後に、2つめのSELECT文で取れるデータをソートししてくっつけたい

1つめのSELECT文で取れるデータ(numが1,2,3)の後に 2つめのSELECT文で取れるデータ(numが4,5,6)をくっつけて、それぞれtextでソートしたい場合

それぞれのSELECT文にORDER BY <条件>を付けても...

(SELECT * FROM test_table WHERE num in (1,2,3) ORDER BY text)
UNION ALL
(SELECT * FROM test_table WHERE num in (4,5,6) ORDER BY text);

👇 上手く動作しません SELECT分は括弧()で囲まないとエラーになりました

+----+------+------+
| id | num  | text |
+----+------+------+
|  8 |    3 | g    |
|  9 |    2 | h    |
| 10 |    1 | j    |
|  5 |    6 | f    |
|  6 |    5 | d    |
|  7 |    4 | i    |
+----+------+------+

それぞれにある、ORDER BYは無視されてしまいました。

Note however, absent an ORDER BY (or GROUP BY) clause on the outermost query, the order that the rows are returned is NOT guaranteed.
If you need the rows returned in a particular sequence, you should include an ORDER BY on the outermost query. In a lot of use cases, we can just use an ORDER BY on the outermost query to satisfy the results.
[出典]: mysql - How to use ORDER BY inside UNION - Stack Overflow

外側にORDER BYを付けないと順序は担保されないようです...
と質問をTwitterに投げていた所

(select *,1 as tb_no from hoge) tb1
union
(select *,2 as tb_no from hoge) tb2

みたいな。カラム指定に固定値を入れられるから、ソート用にテーブル毎の値をいれてあげるといい。
各テーブル内のソートも省略して、
order by table_no, date みたいにするといいかもね。サイトはわからんです！
— がめら (@gamerav) 2017年12月21日

がめら大先輩ありがとうございます！ Twitter最の高!! (無差別凍結だけは辞めて…)

という事で、
カラム指定に固定値を入れ、それをソート条件でその値を利用するとイケるみたいです！

SELECT *, 1 AS row FROM test_table WHERE num in (1,2,3)
UNION ALL
SELECT *, 2 AS row FROM test_table WHERE num in (4,5,6)
ORDER BY row, text;

👇結果

+----+------+------+-----+
| id | num  | text | row |
+----+------+------+-----+
|  8 |    3 | g    |   1 |
|  9 |    2 | h    |   1 |
| 10 |    1 | j    |   1 |
|  6 |    5 | d    |   2 |
|  5 |    6 | f    |   2 |
|  7 |    4 | i    |   2 |
+----+------+------+-----+

やったぜ！₍₍ (ง ˙ω˙)ว ⁾⁾

1つめのSELECT文で取れるデータをソートした後に、2つめのSELECT文で取れるデータをソートししてくっつけたい

1つめのSELECT文で取得したデータをnumでソートした後に、 2つめのSELECT文で取得したデータをtextでソートして
結合したいような場合

先の例で個別にORDER BYを付けても無視されたので、結構ハマりました。
結論を先に言えば、公式のドキュメントに答えが書いてありました。

個々の SELECT ステートメントに対して ORDER BY を使用しても、UNION がデフォルトでは、順序付けされていない行のセットを生成するため、最終的な結果に行が現れる順序には何も影響を与えません。そのため、このコンテキストでは通常、ORDER BY は LIMIT と組み合わせて使用されます。それにより、選択された行の UNION の最終結果での順序に必ずしも影響を与えるわけではないにもかかわらず、SELECT で取得するためのこれらの行のサブセットを決定するために使用されるようになります。ORDER BY が SELECT 内に LIMIT なしで現れた場合、この句はいずれにしても何も効果がないため、最適化によって削除されます。
[出典]: MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.4 UNION 構文

つまり、LIMIT が指定されていれば、それぞれのSELECT文でORDER BYが効くのでは？

(SELECT * FROM test_table WHERE num in (1,2,3) ORDER BY num LIMIT 3)
UNION ALL
(SELECT * FROM test_table WHERE num in (4,5,6) ORDER BY text LIMIT 3);

👇結果

+----+------+------+
| id | num  | text |
+----+------+------+
| 10 |    1 | j    |
|  9 |    2 | h    |
|  8 |    3 | g    |
|  6 |    5 | d    |
|  5 |    6 | f    |
|  7 |    4 | i    |
+----+------+------+

(ꆤ.̫ꆤ)ﾆｯｺﾘ

次のようにしたほうがより確実かもしれません。(上記は、偶然 1つめSELECTデータの後に2つめのSELCTが結合されているだけのかもしれないので)

(SELECT *, 1 AS row FROM test_table WHERE num in (1,2,3) ORDER BY num LIMIT 3)
UNION ALL
(SELECT *, 2 AS row FROM test_table WHERE num in (4,5,6) ORDER BY text LIMIT 3)
ORDER BY row;

👇結果

+----+------+------+-----+
| id | num  | text | row |
+----+------+------+-----+
| 10 |    1 | j    |   1 |
|  9 |    2 | h    |   1 |
|  8 |    3 | g    |   1 |
|  5 |    6 | f    |   2 |
|  7 |    4 | i    |   2 |
|  6 |    5 | d    |   2 |
+----+------+------+-----+

`LIMIT` が使えない場合

取得する数が分かってるとか、取得する上限が決まっているのであれば問題ないのですが、データを全件取得したいとかでLIMITが使えない場合、それぞれのSELECT文にORDER BYを付けても無視されてしまいます。

先の 1つめのSELECT文で取得したデータをnumでソートした後に、 2つめのSELECT文で取得したデータをtextでソートして
結合したいような場合
を LIMITを使わずに実現できるかにチャレンジしてみましたが、私の力では実現できませんでした...
例えば

SELECT T1.* FROM
  (SELECT * FROM test_table WHERE num in (1,2,3) ORDER BY num) AS T1 
UNION 
SELECT T2.* FROM 
  (SELECT * FROM test_table WHERE num in (4,5,6) ORDER BY text) AS T2;

SELECT文をサブクエリにしてみても...

+----+------+------+
| id | num  | text |
+----+------+------+
|  8 |    3 | g    |
|  9 |    2 | h    |
| 10 |    1 | j    |
|  5 |    6 | f    |
|  6 |    5 | d    |
|  7 |    4 | i    |
+----+------+------+

UNIONで結合する限り ORDER BY は無視されてしまいました。

LIMITに大きな数字で設定してしまう

LIMITを使って全件取得する方法を検索しました。

From the MySQL documentation:
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
So getting all rows might look as follows:
SELECT * FROM tbl LIMIT 0,18446744073709551615;
[出典]: [https://stackoverflow.com/questions/15950871/select-all-records-using-mysql-limit-and-offset-query:title]

MySQLのドキュメントに「96番目から最後まで取得したいなら、何か大きな数字を使えば実現できる」と書いてあるから、
LIMITを使って、最初から取得するにはその大きな数字を使えば良い。
という事らしいw パワープレイだwww
ドキュメントに本当に書いていましたw

なので、UNIONを使って全件取得で、それぞれのSELECT文を別にソートする場合は、大きな数字でLIMITを書ければ上手くいきそうです。

(SELECT * FROM test_table WHERE num in (1,2,3,7,8,9) ORDER BY num LIMIT 18446744073709551615)
UNION ALL
(SELECT * FROM test_table WHERE num in (4,5,6,10) ORDER BY text LIMIT 18446744073709551615);

👇 結果

+----+------+------+
| id | num  | text |
+----+------+------+
| 10 |    1 | j    |
|  9 |    2 | h    |
|  8 |    3 | g    |
|  4 |    7 | e    |
|  3 |    8 | a    |
|  2 |    9 | c    |
|  1 |   10 | b    |
|  6 |    5 | d    |
|  5 |    6 | f    |
|  7 |    4 | i    |
+----+------+------+

できました。(ᵒ̤̑ ₀̑ ᵒ̤̑) (データの入る上限がある程度決まっているなら、ここまで大きな数字でなかったり、カラム数をカウントしてLIMITに設定するとかでも良いのかもしれません)

今日のアイカツ格言
「困ったときは公式ドキュメント」
f:id:kikiki-kiki:20171222090214p:plain