PostgreSQLでの UNION ALL と ORDER BY の挙動まとめ

PostgreSQLで複数のSELECT結果を結合する UNION ALL と、その結果の並び替えに使う ORDER BY は、意外と細かいルールがあります。
特に列名の扱いやNULLの並び順を理解しておかないと、意図しない結果になったりエラーになったりします。

1. 基本の書き方
   SELECT a, b FROM c
   UNION ALL
   SELECT a, b FROM d
   ORDER BY a;
   ポイント
   UNION ALL は 重複行を消さない
   UNION は 重複行を自動で削除
   ORDER BY は UNION ALL 後の全体に対して適用される
2. 列名の扱い
   2-1. 最初のSELECTの列名が採用される
   SELECT id, name FROM table1
   UNION ALL
   SELECT id, full_name FROM table2
   ORDER BY name;
   ORDER BY には 最初のSELECTの列名 を使う必要がある
   後続のSELECTの列名 full_name は無視される
   2-2. 存在しない列名を指定するとエラー
   SELECT a, b FROM c
   UNION ALL
   SELECT a, b FROM d
   ORDER BY K; -- ❌ エラー
   ERROR: column "k" does not exist
   K は最初のSELECTに存在しないためエラーになる
   2-3. 列番号で指定可能
   SELECT a, b FROM c
   UNION ALL
   SELECT a, b FROM d
   ORDER BY 1; -- 1列目のaで並び替え
   ORDER BY 2; -- 2列目のbで並び替え
   列番号を使えば、列名が異なっていても並び替え可能
3. NULL の扱い
   PostgreSQLでは NULL は次のように扱われます。
   並び順 NULL の位置
   ASC 最後
   DESC 最初
   SELECT value FROM table1
   UNION ALL
   SELECT value FROM table2
   ORDER BY value ASC NULLS LAST;
   NULLS FIRST / NULLS LAST を使うと位置を明示できる
   UNION ALL 後の全体に対して適用される
4. 個別SELECTのORDER BYは無視される
   SELECT id FROM table1 ORDER BY id DESC
   UNION ALL
   SELECT id FROM table2 ORDER BY id ASC;
   これらの個別SELECTの ORDER BY は無視される
   結合後の全体に対して 1回だけ ORDER BY を書く必要がある
5. 安全に使うためのポイント
   UNION ALL 後の列名は 最初のSELECTの列名が採用
   ORDER BY は最初のSELECTの列名か、列番号で指定する
   NULLの位置を意識する場合は NULLS FIRST / NULLS LAST を明示
   個別SELECTの ORDER BY は意味がない