自己結合の使い方

はじめに

　SQLが提供する結合演算には、その特徴に応じて内部結合、外部結合、クロス結合などさまざまな名前が与えられています。普通、これらの結合の多くは、異なるテーブルまたはビューを対象として行われます。しかし、SQLは結合が同一のテーブルまたはビューに適用されることを禁止していません。同一のテーブルを対象に行う結合を「自己結合（self join）」と呼びます。自己結合は、使いこなせば非常に便利な技術ですが、動作がイメージしにくいため敬遠されがちです。そこで本稿では、この自己結合の便利さを例題を通して学び、その動作を分かりやすく解説します。

　自己結合を理解することは、実務上のテクニックを身につける以外に、もう一つ利点があります。それは、集合指向（set-oriented）というSQLの重要な特徴を理解できることです。オブジェクト指向言語が世界をオブジェクトとして表現するように、SQLは世界を集合として表現します。自己結合は、SQLのこの特徴をうまく利用した技術です。きっと、本稿を読み終わるころには、今まで二次元表に見えていたテーブルが、少し集合らしく見えてくるはずです。

稼働環境

• Oracle
• SQL Server
• DB2
• PostgreSQL
• MySQL（サブクエリを使うものについてはバージョン4.1以上）

対象読者

　GROUP BY、EXISTS述語、通常の等結合、相関サブクエリ、スカラ・サブクエリについての知識があることが望ましいです。

重複順列・順列・組み合わせ

　次のような商品テーブルに、「りんご、みかん、バナナ」の3レコードが登録されているとします。売上を調べる統計表を作成する場合などに、これらの品物の組み合わせを取得したいことがあります。

　「組み合わせ」とひとことで言っても、その種類は2つあります。一つが、並び順を意識した順序対（ordered pair）、もう一つが順序を意識しない非順序対（unordered pair）です。順序対は、<1, 2>のように尖った括弧で、非順序対は{1, 2}のような括弧で表記します。順序対は、順序が違えば別物なので、<1, 2> ≠ <2, 1>ですが、非順序対の場合は順序を無視するので、{1, 2} = {2, 1}です。

　さて、次のように単純に直積を作ると、順序対が得られます。

--重複順列を得るSQL
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1,
       Products P2;

name_1      name_2
------      ------
りんご      りんご
りんご      みかん
りんご      バナナ
みかん      りんご
みかん      みかん
みかん      バナナ
バナナ      りんご
バナナ      みかん
バナナ      バナナ

　一行が一つの順序対を表します。結果行数は重複順列で ₃₂ = 9 です。この結果には冗長な（りんご, りんご）という行が含まれますし、（りんご, みかん）と（みかん, りんご）という順序を変えただけの組み合わせも異なる行として現れます。これは、先に述べたように順序を意識した集合だからです。

　ここから、冗長な集合を排除する変更を考えます。まず、（りんご, りんご）のような同一要素の組み合わせを除外するには、次のように条件を追加した結合を行います。

--順列を得るSQL
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1,
       Products P2
 WHERE P1.name <> P2.name;

name_1      name_2
------      ------
りんご      みかん
りんご      バナナ
みかん      りんご
みかん      バナナ
バナナ      りんご
バナナ      みかん

　「WHERE P1.name <> P2.name」という結合条件によって、同一要素の組み合わせを排除しています。結果行数の計算は順列で ₃P₂ = 6 です。この結合を理解するポイントは、次のような2つのテーブルが本当に2つあるのだと想像することです。

（りんご、りんご）の組み合わせはダメ

　もちろん、P1もP2も、物理的には同じ「Products」テーブルとして格納されています。しかし、SQLにおいて異なる別名が与えられたなら、たとえ同一のテーブルであっても、それらは異なるテーブル（集合）と見なされます。P1とP2はたまたま保持するデータが等しかっただけの、異なる2つの集合として考えられる、ということです。すると、この自己結合の動作は

• P1の「りんご」行の結合対象は、P2の「みかん、バナナ」の2行
• P1の「みかん」行の結合対象は、P2の「りんご、バナナ」の2行
• P1の「バナナ」行の結合対象は、P2の「りんご、みかん」の2行

　というように、異なるテーブルを使う通常の結合と同様に考えることができます。このように考えれば、自己結合の「自己」という接頭辞にも大きな意味はありません。

　さて、この結果も、まだ順序対です。ここからさらに、（りんご, みかん）と（みかん, りんご）のような順序を入れ替えた組み合わせを排除することを考えます。次のSQLを見てください。

--組み合わせを得るSQL
SELECT P1.name AS name_1, P2.name AS name_2
  FROM Products P1,
       Products P2
 WHERE P1.name > P2.name;

name_1      name_2
------      ------
りんご      みかん
りんご      バナナ
みかん      バナナ

　ここでもやはり、P1、P2という2つのテーブルが存在すると考えてください。結果行数の計算は組み合わせで ₃C₂ = 3 です。ここまで絞ってようやく非順序対が得られました。恐らく、私たちが普段「組み合わせ」と言うとき、念頭においているのはこのタイプのものでしょう。

　3つ以上の列を使いたいときも、次のように簡単に拡張できます。

--組み合わせを得るSQL  3列の拡張版
SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
  FROM Products P1,
       Products P2,
       Products P3
 WHERE P1.name > P2.name
   AND P2.name > P3.name;

　この例題のように等号「=」以外の比較演算子である < や >、<> を使って行う結合を「非等値結合」と言います。それを自己結合と組み合わせているので、「自己非等値結合」です。列の組み合わせを作りたいときに多用するので、覚えておくとよいでしょう。

　また、>、< などの比較演算子は数値型の列に限らず、文字型でも辞書順比較として機能するということも、今回のちょっとしたワンポイントです。

重複行を削除する

　重複行というのは、リレーショナル・データベースの世界においてNULLと並んで嫌われる存在です。そのため、これを排除するための方法も数多く考えられています。例えば、先の例題で使った商品テーブルで、「みかん」に重複が生じているテーブルを考えます。このテーブルには恐ろしいことに主キーすら設定されていません（というより、設定できません）。こんなテーブルはすぐにでも「掃除」する必要があります。

重複行の削除

　今回は、自己相関サブクエリを使って重複を削除する方法を紹介します。結合と相関サブクエリは演算としては異なりますが、考え方が似ていて、SQLを同値変換できる場合も多いので、本稿で一緒に扱います。

　重複行は2行でなくとも、何行あってもかまいません。一般に、重複する列が主キーを含まない場合は、主キーを使うことができますが、この例題のように全列について重複する場合は、実装依存のレコードIDを使う必要があります。レコードIDは「どんなテーブルでも使える主キー」という特徴を持つ擬似列だと考えてください。ここではOracleのrowidを使います^（*1）。

--重複行を削除するSQL  その1：極値関数の利用
DELETE FROM Products P1
 WHERE rowid < ( SELECT MAX(P2.rowid)
                   FROM Products P2
                  WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;

　これは、一見しただけでは、動作の分かりづらい相関サブクエリです。そもそも、2つのテーブル間の関連を記述するから「相関」サブクエリという名前なのに、1つのテーブルについて相関というのも、奇妙な表現です。

　この疑問が生じるのは、SQL を見るレベルを間違えているからです。この相関サブクエリも、先の例題と同様、実は次のような瓜二つの集合の関連を記述していると考えてください。

　ポイントは同じで、SQLの中で異なる名前の与えられた集合を、本当に別物として考えることです。このサブクエリは、P1とP2を比較して、名前と値段が等しいレコード集合のうち、その最大のrowidのレコードを返します。

　すると、重複が存在しないりんごとバナナの場合は、「1：りんご」と「5：バナナ」がそのまま返り、条件が不等号なので一行も削除されません。みかんの場合は「4：みかん」が返り、それより小さなrowidを持つレコード「2：みかん」と「3：みかん」の2行が削除されます。

　もうお分かりのように、SQLを実表のレベルで見るというのは、抽象度の低い見方です。「テーブル」「ビュー」というのは記憶方法に応じてつけられた名前ですが、SQLの動作を考える際には、データの記憶方法は（パフォーマンスを除けば）考慮する必要はありません。

　ところで、先の例題でも登場した非等値結合を使うことで、同じ動作をするSQLを書くことができます。どういう動作をしているのか、集合P1とP2を紙に書いて、確かめてみてください。

--重複行を削除するSQL  その2：非等値結合の利用
DELETE FROM Products P1
 WHERE EXISTS ( SELECT *
                  FROM Products P2
                 WHERE P1.name = P2.name
                   AND P1.price = P2.price
                   AND P1.rowid < P2.rowid );