どちらの演算子も 2 つのクエリの結果を互いに積み重ねるため、SQL UNION と UNION ALL はマイナーな選択肢のように見えるかもしれません。そうではない。一言追加すると、結果とデータベースの動作負荷の両方が変わります。

ここではすべてのクエリを SQLite 3.50 で実行し、表示された結果とタイミングはそれらの実行からのものです。基本的な動作は、主要なデータベースにわたる標準 SQL です。ただし、タイミングは独自のデータベース、データ、ハードウェアによって異なるため、ここでのタイミングは一例として扱ってください。

短い答え

UNION は 2 つの結果セットを結合し、異なる行のみを返します。 UNION ALL はそれらを結合し、重複を含むすべてを返します。それがすべての違いであり、この記事の残りの部分では、それがなぜ重要なのかについて説明します。

UNION と UNION ALL を並べて表示

サンプルデータ

オンラインでの注文と店舗での注文を別のテーブルに保管している店を想像してください。 Asha には同じオンライン注文が 2 つあり、店舗で同じ注文が 1 つあることに注意してください。

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

違いを見てください

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

UNION で入力行 5 行が 3 行になり、UNION ALL で 5 行すべてが残りました。注意すべき 2 つの点:

• UNION 最初のテーブル内のものも含め、結果全体から重複を削除しました。テーブル間の比較だけではありません。
• 行は、選択されたすべての列が一致する場合にのみ重複としてカウントされます。

本当に個別のリストが必要な場合は、UNION が正確です。これにより、いずれかのチャネルを通じて購入したすべての顧客に次の特典が提供されます。

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

合計が間違っているバグ

これが、選択が本当に重要である理由です。総収益が必要だとします。上記の注文を合計すると 800 になります。各演算子が何を与えるかを確認してください。

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

UNION バージョンは 200 間違っています。total 列のみを選択したため、3 つの別々の 100 ルピー注文が重複のように見え、1 つに折りたたまれました。エラー メッセージはなく、600 という数字は完全に信頼できる数字のように見えます。この種の静かなバグは、財務報告書に何か月も残る可能性があります。

両方が従わなければならないルール

どちらの演算子にも同じ要件があります。

1. 各 SELECT は 同じ数の列 を返さなければなりません。
2. 列は名前ではなく position によって照合されるため、同じ順序を維持してください。
3. 各位置のデータ型は互換性である必要があります。
4. 結果の列名は first SELECT から取得されます。
5. ORDER BYは最後にあり、結果全体に適用されます。

最初のルールに違反するとエラーが発生します。 SQLite からのメッセージは次のとおりです。

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

2 番目のルールは、エラーなしで失敗する可能性があるため、より危険です。同じタイプの 2 つの列 (first_name と last_name など) を交換すると、クエリが実行され、データが間違ったものになります。 SQLite は型に関しても緩和されていますが、PostgreSQL などのデータベースはより厳密で、明示的な型が必要になる場合があります CAST。

ソースにラベルを付ける

非常に実用的なトリックは、各行がどこから来たのかを示す定数列を追加することです。ラベルが異なるため、異なるテーブルの行を相互の重複として扱うことができなくなりました。

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

データにラベルを付けたら、GROUP BY を使用してデータをグループ化し、チャネルごとに売上をレポートできます。複数のステップを組み合わせる必要がある場合は、共用体を CTE でラップして読み取り可能に保ちます。

パフォーマンス: テストで何が起こったか

重複を削除するには、データベースは通常、行を並べ替えたりハッシュしたりして行を比較する必要があります。 UNION ALL はこのステップを省略できます。それぞれ 500,000 行のテーブルを 2 つ作成し、0 ～ 50,000 の乱数を含めたので、重複がたくさんありました。各クエリを数回実行し、最良の時間を維持しました。

まず、プログラムに行を送り返すのに費やした時間が比較に影響しないように、結果をカウントしました。

この大きな差は、SQLite がすべての行を読み取らなくてもそのカウントを計算できることが原因の 1 つであるため、これを「数千倍高速」と解釈しないでください。ただし、これは核となるアイデアを示しています。UNION は実際の作業を行う必要があり、UNION ALL は実際の作業を行わなかったのです。

次に、すべての行を Python にフェッチすると、結果が反転しました。

ここで、UNION ALL は 20 倍の行を配信したため、さらに時間がかかりました。重複排除が速度を低下させたわけではありません。 100 万行を処理するというものでした。したがって、正直なルールとしては、UNION ALL の方がデータベースの計算コストは​​安くなりますが、結果のサイズも重要になります。オンラインの多くのチャートでは、これを「UNION ALL は常に高速である」と単純化していますが、私たちのテストは、なぜそれがそれほど単純ではないのかを示しています。

また、速度だけを重視して選択しないでください。最初に正しい答えを与える演算子を選択してから調整します。クエリが遅い場合は、SQL クエリの最適化 に関するガイドをお読みください。

それぞれをいつ使用するか

次の場合に UNION ALL を使用します。

• 月次パーティションなど、類似したテーブルのデータをマージしています。
• その後、合計またはカウントを計算します。
• 入力は重複できないことがわかります。
• ログや監査レコードのように、重複には意味があります。

次の場合に UNION を使用します。

• 2 つのシステムにわたるすべての電子メール アドレスなど、一意のリストが必要です。
• 重複するソースには、同じ事実が複数回含まれています。

UNION ALL をデフォルトとして扱い、UNION を意図的に使用してください。各ブランチに WHERE 句を追加して早期にフィルタリングすると、両方の効果が得られます。

交差と除外

関連する 2 つの集合演算子が、異なる質問に答えます。どちらも同じ列規則に従います。

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle は伝統的に EXCEPT ではなく MINUS を使用しており、これらの演算子のサポートはデータベースやバージョンによって多少異なります。

UNION vs JOIN

これらはよく混同されますが、データを異なる方向に結合します。

よくある間違い

• 習慣的に UNION を使用します。 速度が低下したり、有効な行が削除されたりする可能性があります。
• 選択した列が少なすぎます。 ID を省略すると、個別の行が同一に見えます。
• 列の順序が一致していません。 値がエラーなしで間違った列に配置される場合があります。
• 各ブランチに ORDER BY を追加します。 最後に 1 回ソートします。
• UNION を使用して不正な結合をパッチします。 結合が原因で重複が表示される場合は、結合を修正します。
• 結果の順序を仮定します。 ORDER BY を使用しない場合、どちらの演算子でも行の順序は保証されません。

まとめ

毎回 1 つの質問をしてください。同一の行は同じ事実ですか、それとも別の事実ですか?別々の場合、または重複できない場合はUNION ALLを使用してください。一意のリストが必要な場合は、UNION を使用してください。自分のレポートを確認するには、SUM または COUNT の近くにある UNION をコードで検索し、それらの合計が本当に正しいことを確認します。

よくある質問

学び続ける