SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

達人に学ぶSQL

SQLで集合演算

集合指向言語としてのSQL:その4


  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード サンプルコード (1.9 KB)

SQLは集合論をその基礎の一つとする言語ですが、これまで、SQLが集合演算の整備を怠ってきたことも手伝って、その機能は十分に活用されてきませんでした。しかし近年、ようやくSQLにおいても基本的な集合演算の機能が出揃い、本格的な応用が可能になってきました。本稿では、SQLの集合演算を利用したSQLを紹介し、その背景にある考え方を解説します。

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 SQLが集合論に立脚する言語であるということは、この連載で一貫して強調してきたテーマの一つです。その特性のゆえに、SQLは「集合指向言語」と呼ばれていますし、実際、集合的な観点から見たときに初めて、その強力さが理解できると私は考えています。しかし現実には、SQLのこの側面は長らく無視されてきました。

 その背景には、SQLにも責任の一端があります。というのも、SQLはちょっと前まで、高校で習う程度の基本的な集合演算子すら持っていなかったからです。和(UNION)こそSQL-86からの古参ですが、交差(INTERSECT)と差(EXCEPT)が標準に入ったのはSQL-92ですし、除算(DIVIDE BY)が未だに標準化されていないことは、前にも述べました。だから、SQLが言語として不完全だという批判は、理由のないものではなかったのです。

 しかし、現在では標準SQLに基本的な集合演算子が出揃い、それと歩調を合わせて実装も進み、ようやく本格的な応用が可能になってきました。本稿では、集合演算を利用した便利なSQLを紹介し、その考え方を解説することで、これまでとは違った角度からSQLの本質に迫りたいと思います。

稼働環境

  • Oracle
  • SQL Server
  • DB2
  • PostgreSQL
  • MySQL(バージョン4.1以上 ただしINTERSECTEXCEPTを利用するクエリは使えない)

対象読者

 集合演算子の基本的な使い方を知っている方。相関サブクエリについて知識があると望ましいので、未読の方は「相関サブクエリで行と行を比較する」を先に読むと良いでしょう。

導入―集合演算に関するいくつかの注意点

 SQLの集合演算子は、その名の通り、入力に「集合」を取る演算であり、要するに実装レベルではテーブルやビューを引数に取る演算です。中学や高校で習う集合代数と似ているので、直観的には理解しやすいと思いますが、SQLの場合、いくつか独特な特徴があるので注意が必要です。

注意1:SQLの扱う集合は重複行を許す多重集合のため、それに対応するALLオプションが存在する

 通常、集合論で集合と言えば、重複する要素を認めません。だから {1, 1, 2, 3, 3, 3 } という集合は端的に {1, 2, 3 }と同じと見なされます。しかしリレーショナル・データベースにおけるテーブルは、重複行を認める多重集合(multiset, bag)です。

 その結果、SQLの集合演算子にも、重複を認めるバージョンと認めないバージョンの2通りが用意されています。通常、UNIONINTERSECTをそのまま使うと、結果から重複行を排除します。もし重複行を残したい場合は、ALLオプションを付けて、UNION ALLのように記述します。ちょうどSELECT句のDISTINCTオプションと反対の扱いになっています。しかし、なぜか「UNION DISTINCT」のような書き方は許されていません。

 この2通りの使い方には、演算の結果以外にもう1つ違いが存在します。それは、集合演算子は重複排除のために暗黙のソートを発生させるが、ALLオプションを付けるとソートが行われないのでパフォーマンスが向上する、という点です。これは効果的なパフォーマンス・チューニングなので、重複を気にしなくてよい場合、または重複が発生しないことが確実な場合には、ALLオプションを付けるとよいでしょう。

注意2:演算の順番に優先順位がある

 標準SQLでは、UNIONEXCEPTに対して、INTERSECTの方が先に実行されるよう定められています。従って、UNIONINTERSECTを併用するときにUNIONを優先的に実行したい場合は、括弧で明示的に演算の順序を指定せねばなりません。

注意3:DBMSごとに集合演算子の実装状況にバラツキがある

 先述の通り、初期のSQLは集合演算の整備を怠ってきました。そのツケとして、DBごとの実装状況も統一性がありません。SQLServerは2005バージョンからINTERSECTEXCEPTをサポートしましたが、MySQLはまだ両方とも持っていません(中期的な将来に計画されている新機能に含まれています)。また、Oracleのように、EXCEPTMINUSという別の名前で持っているDBもあります。面倒ですが、Oracleユーザーの方はEXCEPTをすべてMINUSに置き換えて使ってください。

注意4:除算の標準的な定義がない

 四則演算のうち、和(UNION)、差(EXCEPT)、積(CROSS JOIN)は標準に入っています。しかし、残る1つ、商(DIVIDE BY)は、諸事情により標準化が遅れています(この「諸事情」は「HAVING句の力」に詳しく書いたのでそちらを参照してください)。そのため、除算をするときは自前でクエリを作る必要があります。

テーブル同士のコンペア――集合の相等性チェック:基本編

 さて、それでは集合演算の実践的な応用を見ていきましょう。

 DB環境を移行したり、バックアップと最新環境を比較したい場合など、2つのテーブルが等しいか否かを調べたいことがあります。ここで言う「等しい」とは、行数も列数もデータ内容も同じ、つまり「集合として等しい」という意味です。例えば、次の「tbl_A」と「tbl_B」は、テーブル名が違うだけで、集合としては同じです。

名前が違うだけで、中身は同じ二つのテーブル
名前が違うだけで、中身は同じ二つのテーブル

 こういう等しいテーブル同士が等しいと分かり、等しくないテーブルが等しくないと分かる方法はないでしょうか。たとえるなら、ファイルに対するコンペアを、テーブルに対して行うイメージです。サンプルのように数行程度なら目で確認しても間違いは少ないでしょうが、数百列とか数千万行の規模では無理な相談です。

 これを実現する方法は、2通りあります。まずはUNIONだけを使う簡単な方法から見ていきましょう。事前に、「tbl_A」と「tbl_B」の行数は同じであることは確認済みと仮定します(そもそも行数が違ったらその時点で終わりです)。

 このサンプルだと、両テーブルとも行数は3です。すると、次のクエリの結果も3であれば、テーブル同士が相等であることが分かります。逆に、結果が3より大きくなれば、相等ではありません。

このクエリの結果が tbl_Aとtbl_Bの行数と一致すれば、両者は等しいテーブル
SELECT COUNT(*) AS row_cnt
  FROM ( SELECT *
           FROM tbl_A
         UNION
         SELECT *
           FROM tbl_B ) TMP;
結果
row_cnt
---------
      3

 なぜそう言えるのでしょうか? 「導入」の注意1を思い出してください。SQLの集合演算子は、ALLオプションを付けなければ重複行を排除します。それゆえ、「tbl_A」と「tbl_B」が同じなら、重複が排除されてきれいに重なりあうのです。

 必然的に、次のような1行だけ相違するテーブルに対しては、結果が「4」になります。相違する行は、重複排除した後も「一体化」せずに残らざるをえないからです。

keyが「B」の行が相違するサンプル・データ:結果は「4」になる
keyが「B」の行が相違するサンプル・データ:結果は「4」になる

 このクエリは、NULLを含むテーブルにも正しく動作しますし、列数や列名、データ型を一切指定せずに使えるのが便利なところです。UNIONしか使わないのでMySQLでも動きます。もちろん、テーブルの一部だけを比較することもできます。その場合は、比較したい列名を指定し、WHERE句で条件を設定すればOKです。

 以上の例からも明らかなように、SQLのUNIONには、任意のテーブルSについてS UNION S = Sとなります。これは、UNIONが非常に重要なある性質を持つことを意味します。それは、数学で冪等性(べきとうせい、idempotency)と呼ばれる性質です。もともと群論などの抽象代数で使われる概念で、いくつか意味がありますが、今回に関係する意味を平たく言うと「二項演算子 * の任意の入力Sについて、S * S = Sが成り立つ」ということです。UNIONはこの意味で冪等です。

 また、プログラミングの分野では、この原義を少し拡張して、「繰り返し処理を実行しても、1度だけ実行した場合と結果が同じになる」という意味で使います。例えば身近な例としては、C言語のヘッダーファイルは冪等性を持つよう設計されています。同じファイルを何度インクルードしても、1度インクルードした場合と結果が変わらないからです。同じ意味で、HTTPのGETコマンドも冪等です。同じ要求を繰り返し発行しても安全なようになっているからです。また、この性質は、特にユーザーインターフェイスにおいて重要な役割を果たします。ボタンを何回連打しても1度押したのと同じこと、というのは安全なインターフェイス設計の基本です。

 集合演算のUNIONの場合、「S UNION S」を1つの処理単位と見れば、何度実行しても結果が変わりませんから、これも冪等と言えます。従って、三つ以上のテーブルが等しいかどうかを比較することも可能です。

同じ集合をいくつ足しても結果は不変
S UNION S UNION S UNION S ……  UNION S = S

 1つ注意が必要なのは、「UNION ALL」は演算を繰り返すたびに結果が変化するので、冪等性が成立しないことです。似た理由から、重複行を持ったテーブルに対してはUNIONも冪等性を失います。つまり、この美しく強力な性質が成り立つのは、あくまで集合の世界の話であって、多重集合の世界では通用しないのです。皆さん、主キーは大事だよ、ということです。

 さて、それでは先に進む前に、ちょっとクイズを出しましょう。実は、集合演算子には、UNION以外にも冪等性を持つものがあります。それは何でしょう? 次はその演算子も使います。

次のページ
テーブル同士のコンペア―集合の相等性チェック:応用編

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
達人に学ぶSQL連載記事一覧

もっと読む

この記事の著者

ミック(ミック)

日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。主な著書・訳書:『達人に学ぶSQL徹底指南書 第2版』(2018)『SQL実践入門』(2015)Joe Celko『プログラマのためのSQL 第4版』(2015)翔泳社 - 著者ページ:https://www.shoeisha.co.jp/book/author/3964著者個人ページ:http://mickindex.sakura.ne.jp/

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/1304 2007/10/09 13:03

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング