外部結合の使い方

結果のフォーマット整形と集合演算を外部結合で行う

完全外部結合

　前半では、主に外部結合の応用的な側面をクローズアップしました。後半は少し趣向を変えて外部結合そのものの特性を、集合指向的な観点から考えてみたいと思います。標準SQLでは、3種類の外部結合の構文が定義されています。すなわち、

• 左外部結合（LEFT OUTER JOIN）
• 右外部結合（RIGHT OUTER JOIN）
• 完全外部結合（FULL OUTER JOIN）

　です。このうち、左外部結合と右外部結合の間に機能的な差はありません。マスタに使うテーブルを演算子の左に書けば左外部結合、右に書けば右外部結合を使います。この2つについては、皆さんもよくご存知でしょう。ここでは、3つの中では比較的重視されていない完全外部結合を取り上げます。というのも、これを集合演算の観点から見ると、外部結合の面白い特徴が見えてくるからです。

　完全外部結合がどういう演算かは、言葉で説明するより目で見た方が分かりやすいので、早速、簡単なテーブルを使って見ていきましょう。

Class_A

識別子(id)	名前(name)
1	田中
2	鈴木
3	伊集院

Class_B

識別子(id)	名前(name)
1	田中
2	鈴木
4	西園寺

　上の2つのクラスに所属する生徒のうち、田中、鈴木の2人は両方のテーブルに存在します。しかし、伊集院と西園寺は片方のテーブルにしか存在しません。完全外部結合とは、こういうデータ内容が不一致の2つのテーブルから、情報を欠落させずに結果を得るための方法です。言うならば「両方をマスタに使う結合」です。

完全外部結合は情報を「完全」に保存する

SELECT COALESCE(A.id, B.id) AS id,
       A.name AS A_name,
       B.name AS B_name
FROM Class_A  A  FULL OUTER JOIN Class_B  B
  ON A.id = B.id;

結果

id    A_name    B_name
----  --------  --------
1     田中       田中
2     鈴木       鈴木
3     伊集院
4               西園寺

　2つのテーブルに存在する4人全員が結果に現れています。COALESCEは、可変個の引数をとって、NULLでない最初の引数を返す標準関数です。左（または右）外部結合の場合、マスタに使えるテーブルはどちらか一方だけなので、伊集院と西園寺の両方を同時に得ることはできません。完全外部結合の「完全」とは「情報を完全に保存する」という意味です。

　なお、完全外部結合を使えない環境で同じ結果を得るには、左外部結合の結果と右外部結合の結果をUNIONします（※注1参照）。

完全外部結合が使えない環境での代替方法

SELECT A.id AS id, A.name, B.name
  FROM Class_A  A   LEFT OUTER JOIN Class_B  B
    ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
  FROM Class_A  A  RIGHT OUTER JOIN Class_B  B
    ON A.id = B.id;

　これでも同じ結果が得られますが、冗長ですし、結合を2回繰り返した上にUNIONを使うのは、パフォーマンス面でも誉められません。

　ところで、少し視点を変えてみると、結合を集合演算とみなすことができます。内部結合が積集合（INTERSECT、「交差」とも呼ぶ）、完全外部結合が和集合（UNION）に、それぞれ相当します。ベン図で描けば下図のようになります。

内部結合は積集合（INTERSECT）

完全外部結合は和集合（UNION）

　「情報を欠落させない」という点において、UNIONと外部結合は良く似ています（その意味でMERGE文とも似ている）。次段では、外部結合のこの特性を利用して、実際に集合演算をしてみましょう。

※注1

　例えば、MySQLは完全外部結合をまだ実装していません。「中期的な将来に計画されている新機能」に含まれています。
 

外部結合で集合演算

　SQLは集合論を基礎としていますが、意外なことに、最近まで基本的な集合演算の機能すら持っていませんでした。UNIONはSQL-86からの古参ですが、INTERSECTとEXCEPTが取り入れられたのはSQL-92ですし、除算が未だに標準化されていないことは、前回述べたとおりです。また各DBMSの実装状況も不十分で、バラつきがあります（MySQLはINTERSECTとEXCEPTを持っていないし、Oracleのように、EXCEPTをMINUSという別の名前で持っているケースもある）。集合演算子はソートを発生させるので、パフォーマンス上の問題を引き起こす可能性もあります。それゆえ、集合演算子の代用案を知っておくことには意味があります。

　では、和と交差に続いて、差の求め方を考えましょう。先の完全外部結合の結果を注意してみると、Aクラスには存在するけど、Bクラスには存在しない伊集院の行はB_name列がNULLになっていることが分かります。逆に、Bクラスには存在するけど、Aクラスには存在しない西園寺は、A_name列がNULLです。つまり、結合結果に対してNULLかどうかの条件を設定することで、差集合を求められます。

外部結合で差集合を求める：A-B

SELECT A.id AS id,  A.name AS A_name
  FROM Class_A  A LEFT OUTER JOIN Class_B B
    ON A.id = B.id
 WHERE B.name IS NULL;

結果

id    A_name
----  --------
3     伊集院

外部結合で差集合（A-B）

外部結合で差集合を求める：B-A

SELECT B.id AS id, B.name AS B_name
  FROM Class_A  A  RIGHT OUTER JOIN Class_B B
    ON A.id = B.id
 WHERE A.name IS NULL;

結果

id    B_name
----  --------
4     西園寺

外部結合で差集合（B-A）

完全外部結合で排他的和集合を求める

　次に、集合AとBの排他的和集合を考えます。SQLはこのための演算子を持っていないので、集合演算子を使うなら、(A UNION B) EXCEPT (A INTERSECT B)または(A EXCEPT B) UNION (B EXCEPT A)という方法になります。どちらにせよかなり面倒ですし、コストも高くつきます。

　再び完全外部結合の結果をじっと見つめると……分かりましたか？

SELECT COALESCE(A.id, B.id) AS id,
       COALESCE(A.name , B.name ) AS name
  FROM Class_A  A  FULL OUTER JOIN Class_B  B
    ON A.id = B.id
 WHERE A.name IS NULL 
    OR B.name IS NULL;

結果

id    name
----  --------
3     伊集院
4     西園寺

完全外部結合で排他的和集合

　このようにWHERE句の条件を変えることで、いろいろな集合演算を表現できます。これで集合の和、差、交差が求められました。では、商はどうでしょう。実は、これも外部結合で作れます。つまり、前回紹介した関係除算を、外部結合で書けるのです。前回と同じ「Items」テーブルと「ShopItems」テーブルを使うなら、次のようになります。

外部結合で関係除算：差集合の応用

SELECT DISTINCT shop
  FROM ShopItems SI1
WHERE NOT EXISTS
      (SELECT I.item 
         FROM Items I LEFT OUTER JOIN ShopItems SI2
           ON I.item   = SI2.item 
          AND SI1.shop = SI2.shop
        WHERE SI2.item IS NULL) ;

結果

shop
------
仙台
東京

　このクエリの意味は、「Items」テーブルから、各店舗ごとの商品を引き算した結果が空集合であれば、その店舗は「Items」テーブルの商品をすべて揃えている、ということです。ON句の「SI1.shop = SI2.shop」という相関サブクエリによって「各店舗ごとの」という条件を記述しています（※注2参照）。この方法は集合の差を利用しているので、より直接的にEXCEPTを使って書くこともできます。練習問題として、書き換えてみてください。

※注2

　私が確認した限り、Oracle10gとMySQL5.0は結合条件での相関名の使用をサポートしていないため、このクエリは動きませんでした。PostgreSQL8.1では正しく動作します。
 

おわりに

　最後に、外部結合の構文について触れておきます。SQLは非常に方言の多い言語ですが、中でも外部結合は、Oracleなら(+)、SQLServerなら*=を使うなど、特に実装依存の強い部分です。ソースの汎用性を考えるなら、こうした独自仕様の構文は避けて、ANSI標準に従うべきです。そのため、本稿でも標準的な構文に統一しています。また、「OUTER」は省略可能なので、「LEFT JOIN」や「FULL JOIN」という書き方もできますが（これは一応、標準SQLでそう認められている）、内部結合に対して外部結合であるということを明示するために、省略せずに書くのが良いでしょう。皆さんも普段の業務で少し意識してみてください。

　それでは、今回の要点です。

1. SQLは帳票作成のための言語ではないので、基本的にフォーマット整形には不向き。
2. 必要に迫られたときは外部結合やCASE式を駆使して乗り切ろう。
3. 入れ子の表側を作るときはマスタの直積を作ってから結合一発でキメる。
4. 外部結合は集合演算と類比的に考えられる。その観点から、さまざまな集合演算を表現できる。

　外部結合は日常的によく利用されている技術ですが、そういうなじみ深い技術からも応用が広がっていくところが、SQLの面白さの一つです。

参考資料

1. 『標準SQLガイド　改訂第4版』 C.J.Date ・ Hugh Darwen 著、Quipu LLC 訳、アスキー、1998年12月
   外部結合の標準的な構文については第11章を参照。

2. 『プログラマのためのSQL 第2版』 Joe Celko 著、秋田 昌幸 訳、ピアソンエデュケーション、2001年4月
   外部結合を使った集合演算については「26.2.1 外部結合と集合の差」、差集合を利用した関係除算については「19.2.6 集合演算子による除算」、繰り返し項目を持つテーブルのどのあたりが泣かせどころか分からなかった人は第25章「SQLにおける配列」を、それぞれ参照。

3. 『SQLパズル 第2版』 Joe Celko 著、ミック 訳、翔泳社、2007年11月
   初版から10年近くの歳月を経て、ついに出ました。『SQLパズル 第2版』です。ページ数倍増で新しい問題が多く採録され、既存の問題にも新しい解法が追加されました。やはりというか、SQLの新機能であるOLAP関数を使った解が目立ちます。実務的な問題だけでなく、数独をSQLで解くなんていう遊び心あるパズルも含まれていて楽しい。

4. 『Doing Bad Things Well -- Part II』（パズルコーナーのみ） Joe Celko 著、1997年11月
   「外部結合で行列変換　その2（列→行）」はこのパズル問題を改作したもの。