union allがデフォルト。unionは特殊な場合のみ使う

SQL


重複行を削りたいならunionを使う。重複行を生かしたいならunion allを使う。
ここまではよいとして、どっちでもよい場合、例えば

  • 重複行があり得ない場合
  • IN句のサブクエリなので重複行があってもなくても結果に影響しない場合

にどうするか。


以前はunionをデフォルトにして、重複行を生かしたいときだけunion allしていたが、今は逆に、union allをデフォルトにして重複行を削りたいときだけunionする、という方針にしている。理由はいくつかあるが、allを付けると Sort->Unique の処理が走らないのでパフォーマンス上有利になるはず、というのが大きい。
絶対に重複行が発生しないことが分かっているクエリでも、unionを使うとSort->Uniqueの処理が走ってしまうDBMSは多い。
例えばPostgreSQL：


UNION

bench=# explain analyze
select tid, filler from tellers where tid between 1 and 50
union
select tid, filler from tellers where tid between 101 and 150
;
                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=24.68..25.42 rows=98 width=89) (actual time=0.528..0.890 rows=100 loops=1)
   ->  Sort  (cost=24.68..24.93 rows=98 width=89) (actual time=0.525..0.632 rows=100 loops=1)
         Sort Key: public.tellers.tid, public.tellers.filler
         Sort Method:  quicksort  Memory: 30kB
         ->  Append  (cost=0.00..21.44 rows=98 width=89) (actual time=0.029..0.388 rows=100 loops=1)
               ->  Index Scan using tellers_pkey on tellers  (cost=0.00..10.23 rows=49 width=89) (actual time=0.024..0.100 rows=50 loops=1)
                     Index Cond: ((tid >= 1) AND (tid <= 50))
               ->  Index Scan using tellers_pkey on tellers  (cost=0.00..10.23 rows=49 width=89) (actual time=0.009..0.079 rows=50 loops=1)
                     Index Cond: ((tid >= 101) AND (tid <= 150))
 Total runtime: 1.040 ms
(10 rows)


同じクエリをunion allでつなげるとソートしなくなる。
UNION ALL

bench=# explain analyze
select tid, filler from tellers where tid between 1 and 50
union all
select tid, filler from tellers where tid between 101 and 150
;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..21.44 rows=98 width=89) (actual time=0.027..0.389 rows=100 loops=1)
   ->  Index Scan using tellers_pkey on tellers  (cost=0.00..10.23 rows=49 width=89) (actual time=0.024..0.099 rows=50 loops=1)
         Index Cond: ((tid >= 1) AND (tid <= 50))
   ->  Index Scan using tellers_pkey on tellers  (cost=0.00..10.23 rows=49 width=89) (actual time=0.009..0.079 rows=50 loops=1)
         Index Cond: ((tid >= 101) AND (tid <= 150))
 Total runtime: 0.531 ms
(6 rows)


この現象はサブクエリでunionまたはunion allしても発生する。
またOracle 11gやSQL Server 2005でも同様になる。