6.2. 自己結合

自己結合とは同じテーブル同士の結合のことをいいます。結合は 2つ以上のテーブルに対して行われますが、それらが別のテーブルである必要はありません。自己結合は 2つのまったく同じテーブルの結合として実行されます。

テーブルの自己結合を行う際に問題なのは、テーブルの名前も列の名前も全て同じだという点です。これを解決するために、テーブルに一時的な名前を付けます。この一時的な名前のことをエイリアスといいます。範囲変数または相関変数とも呼びます。sample.17-3 は、商品表の単価が同じである商品名と単価を表示するものです。

/* テーブルの結合 */

  SELECT A.商品名,A.単価 FROM 商品表 A,商品表 B
         WHERE A.単価 = B.単価
         AND A.商品コード <> B.商品コード ;

/* 実行結果 */

 商品名 単価
 ───── ────
 JUICE  400
 TEA    400

エイリアスはクエリの FROM 句に定義します。sample.17-3 のように、テーブル名のあとにスペースを空け、エイリアスを指定すれば定義は完了です。sample.17-3 の WHERE 句で、「A.商品コード <> B.商品コード」としているのは、これを指定しないと全ての商品の単価が表示されてしまうからです。'JUICE' と 'TEA' は 2 回ずつ表示されてしまいます。テーブル A とテーブル B は同じ商品表なので、「A.単価 = B.単価」は全ての行において成立してしまいます。ですから、「A.単価 = B.単価」が成立しても商品コードが同じものは除いてしまうことが必要です。もし、「A.商品コード <> B.商品コード」がなければ下のサンプルのようになります。

/* テーブルの結合 */
  SELECT A.商品名,A.単価 FROM 商品表 A,商品表 B
         WHERE A.単価 = B.単価 ;

/* 実行結果 */

 商品名 単価
 ───── ────
 BEER   500
 JUICE  400
 TEA    400
 JUICE  400
 TEA    400
 WINE   650

自己結合の最も一般的な用途は循環リレーションシップへの対応といわれています。自分のテーブルに存在する列を外部キーとして指定するような、テーブルに循環型の外部キーが含まれる場合があるときに、自己結合はよく用いられます。

ここで説明用のテーブルを一つ定義します。次の従業員表では、上司列が従業員ナンバーを参照する外部キーになっています。どの社員も上司を持っていて、上司自身も社員であるため、従業員表に定義されるというものです。社員と上司の組み合わせを参照する時は、sample.17-5 のように SQL を記述します。

/* テーブルの結合 */

  SELECT A.従業員ナンバー,A.従業員名,B.従業員名
         FROM 従業員表 A,従業員表 B
         WHERE A.上司 = B.従業員ナンバー ;

/* 実行結果 */

 従業員ナンバー   従業員名  従業員名
 ────────────── ──────── ────────
 184            KUSANAGI INAGAKI
 235            KATORI   KIMURA
 135            INAGAKI  KIMURA

実習課題１

販売管理データベースから、顧客番号が '1007' の顧客と同じ自動車を購入した顧客の顧客番号を参照する SQL 文を記述しなさい。ただし、顧客番号 '1007' は表示しないようにすること。

解答例はこちら