UNION ALLとソート

ORDER BY句の無いクエリは結果セットの順序を保証しないと100万回学んだのに、UNION ALLの結果がソートされないことを期待してしまいました。

期待していたこと
UNIONはソートする（まちがっている）
UNION ALLはソートしない（まちがっている）
旅は続く

※PostgreSQLです。また、「期待していたこと」と「旅は続く」のクエリおよび結果は、実際に実行したものを改変したイメージです。

期待していたこと

CREATE TABLE table1 AS (SELECT 1 AS col1);
CREATE TABLE table2 AS (SELECT 2 AS col2);

SELECT col1 FROM table1
UNION ALL
SELECT col2 FROM table2;

期待した結果。

col1
1
2

実際に得られた結果。

col1
2
1

とても悲しい。以下、このような結果を期待した経緯が続きます。

UNIONはソートする（まちがっている）

まずはUNIONとUNION ALLのおさらい。

クエリ1 UNION [ALL] クエリ2

UNIONはクエリ1の結果にクエリ2の結果を付加します。ALLが指定されていない場合は、その結果から重複行を削除します。

クエリ1の結果とクエリ2の結果に重複行が存在しないのであればUNION ALLを使うべきです。UNIONは重複行を排除するためにコストの高いソートを実行します。その副作用として結果セットはソートされた状態で返却されます。

CREATE TABLE table1 AS (SELECT generate_series(2, 1, -1) AS col1);
CREATE TABLE table2 AS (SELECT generate_series(3, 2, -1) AS col2);

SELECT col1 FROM table1
UNION
SELECT col2 FROM table2;

テーブル1の内容。

col1
3
2
1

テーブル2の内容。

col2
4
3
2

結果。

col1
2
4
1
3

ソートされてないじゃん！！

、、、落ち着いて実行計画を見ましょう。

-- UNIONの実行計画
EXPLAIN
SELECT col1 FROM table1
UNION
SELECT col2 FROM table2;

HashAggregate  (cost=160.25..211.25 rows=5100 width=4)
  Group Key: table1.col1
  ->  Append  (cost=0.00..147.50 rows=5100 width=4)
        ->  Seq Scan on table1  (cost=0.00..35.50 rows=2550 width=4)
        ->  Seq Scan on table2  (cost=0.00..35.50 rows=2550 width=4)

HashAggregateが選択されていました。都合が悪いのでoffにしておきましょう。

-- オプティマイザがHash Aggregateを選びにくくする
set enable_hashagg = 'off';

-- UNIONの実行計画（リベンジ）
EXPLAIN
SELECT col1 FROM table1
UNION
SELECT col2 FROM table2;

Unique  (cost=461.57..487.07 rows=5100 width=4)
  ->  Sort  (cost=461.57..474.32 rows=5100 width=4)
        Sort Key: table1.col1
        ->  Append  (cost=0.00..147.50 rows=5100 width=4)
              ->  Seq Scan on table1  (cost=0.00..35.50 rows=2550 width=4)
              ->  Seq Scan on table2  (cost=0.00..35.50 rows=2550 width=4)

ソートしてますね。結果も以下のとおり。

col1
1
2
3
4

今まさに直面したように、明示的にORDER BYを指定しない限り、順序は保証されません。PostgreSQLのキモチ次第です。UNIONの結果がソートされるなんて考えは浅はかなのです。

UNION ALLはソートしない（まちがっている）

「UNIONは重複行を排除するためにソートする」
→「UNION ALLは重複行を排除しない」
→「UNION ALLはソートしない」

浅はかな考えでしょう？

でも、私がソートしないと考えたのは他に理由がありそうです。UNION ALLは2つのクエリをガッチャンコするというイメージに支配されていたんですよ。↓が私の脳内です。

-- クエリ1
SELECT col1 FROM table1
UNION ALL
-- クエリ2
SELECT col2 FROM table2;

それぞれ独立のクエリと考えていたようで、クエリ1の結果とクエリ2の結果が混ざることにモヤッとしたのかもしれません。SELECTの構文が答えを与えてくれます。

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

UNIONいた。ORDER BYの手前にいた。脳内イメージを書き換えます。

-- クエリ1
SELECT col1 FROM table1
-- クエリ1のUNION句
UNION ALL SELECT col2 FROM table2;

完全に理解した。

つまり↓は構文エラーになる。

-- クエリ1
SELECT col1 FROM table1 ORDER BY col1
-- クエリ1のUNION句
UNION ALL SELECT col2 FROM table2;

過去の私ならやっていそうですね。言ってやりましょう。

「「「目を醒ませ　僕らの世界が何者かに侵略されてるぞ」」」

完全に理解した現在の私ならば以下のように書きます。

-- クエリ1
SELECT col1 FROM table1
-- クエリ1のUNION句
UNION ALL SELECT col2 FROM table2
-- クエリ1のORDER BY句
ORDER BY col1;

スッキリしました。普通のSELECT文と認識できれば、UNION ALLが含まれていようと順序が保証されないことに納得です。とても当たり前のことを言っています。

何度でも自分に言い聞かせますが、ORDER BY句の無いクエリは結果セットの順序を保証しないし、UNIONは聴いたほうがいい。

UNION

OxT
アニメ
¥255

provided courtesy of iTunes

旅は続く

冒頭に戻ります。これでいいんです。

CREATE TABLE table1 AS (SELECT 1 AS col1);
CREATE TABLE table2 AS (SELECT 2 AS col2);

SELECT col1 FROM table1
UNION ALL
SELECT col2 FROM table2
-- 明示的にソートする
ORDER BY col1;