【MySQL】UNIONするとソートが効かなくなる

やりたいこと

2つのテーブルから条件に合うデータを抽出し、それぞれID順にソートしてから結合する。

例

dogsテーブルとcatsテーブルから、5歳未満のレコードを抽出し、それぞれID順にソートしてから結合する。

• dogsテーブル

id	name	age
1	ムギ	9
2	ココ	3
3	ソラ	8
4	モカ	2

• catsテーブル

id	name	age
1	レオ	6
2	ルナ	1
3	ベル	7
4	キナコ	4

• 期待する結合結果

id	name	age
2	ココ	3
4	モカ	2
2	ルナ	1
4	キナコ	4

NGな書き方

(SELECT * FROM dogs WHERE age < 5 ORDER BY id)
UNION
(SELECT * FROM cats WHERE age < 5 ORDER BY id);

結合結果

id	name	age
4	モカ	2
2	ココ	3
2	ルナ	1
4	キナコ	4

dogsテーブルがID順にならなかった。
（設定しているINDEXが原因だと予想しているが、）ソートが効いていない。

ドキュメントを見てみる

個々の SELECT ステートメントに ORDER BY を使用すると、UNION では順序付けられていない行のセットがデフォルトで生成されるため、最終結果に行が表示される順序については何も意味しません。 したがって、このコンテキストの ORDER BY は通常、SELECT 用に取得する選択済の行のサブセットを決定するために LIMIT とともに使用されますが、最終的な UNION 結果の行の順序には必ずしも影響しません。 SELECT に LIMIT がない状態で ORDER BY が表示された場合は、どのような場合にも効果がないため最適化されます。

ORDER BY は LIMIT と一緒に使わないと無視される

OKな書き方

LIMIT を設定することで期待した結果が得られた（が、微妙）。

(SELECT * FROM dogs WHERE age < 5 ORDER BY id LIMIT 10)
UNION
(SELECT * FROM cats WHERE age < 5 ORDER BY id LIMIT 10);

結合結果

id	name	age
2	ココ	3
4	モカ	2
2	ルナ	1
4	キナコ	4

採用した書き方

LIMIT を設定したくなかったので、他の書き方を採用した。

(SELECT 1 as sort, * FROM dogs WHERE age < 5)
UNION
(SELECT 2 as sort, * FROM cats WHERE age < 5)
ORDER BY sort, id;

結合結果

sort	id	name	age
1	2	ココ	3
1	4	モカ	2
2	2	ルナ	1
2	4	キナコ	4

不要なカラムは後続処理で消した