基本的な結合 / 自己結合


テーブルの結合とは、複数のテーブルの間に定義されたリレーションシップをもとに、複数のテーブルを結合してデータを引き出すことです。

実際に新しいテーブルを作成するわけではなく、既存のテーブル間のリレーションシップから、データをよりわかりやすい状態で参照できるようにします。

使用する表

受注表

受注番号
CHAR(5)
顧客コード
CHAR(4)
商品コード
CHAR(4)
受注個数
INTEGER
納品日
CHAR(8)
10001 001 102 300 20010401
10002 002 101 200 20010401
10003 004 101 150 20010401
10004 004 102 80 20010402
10005 002 104 110 20010402
10006 001 103 250 20010402
10007 003 102 45 20010402
10008 004 101 135 20010403
10009 003 103 65 20010403
10010 001 101 290 20010403
10011 003 103 60 20010404
10012 004 101 175 20010404

顧客表

顧客コード
CHAR(4)
顧客名
CHAR(20)
001 KUROKIYA
002 ONSIDE
003 FIRST HOUSE
004 KUSHINANA

商品表

商品コード
CHAR(4)
商品名
CHAR(20)
単価
INTEGER
101 BEER 500
102 JUICE 400
103 TEA 400
104 WINE 650

基本的な結合

受注表からは受注番号に対応する顧客コードと商品コードを知ることができます。
そして、これらの列は外部キーとなっているため、リンクされる顧客表と商品表からコードに対する名称を知ることが可能となります。

つまり受注番号、顧客名、商品名、受注個数及び納品日の列を持つ表を、表の結合により作成することができるのです。

ちなみに、受注表における受注番号、顧客表における顧客コード及び商品表における商品コードは各テーブルの主キーとなっています。

テーブルを結合するには、これまでのテーブルのデータを参照する時と同様に SELECT 文を用います。

SELECT 文に続けて、表示する列名を指定し、FROM のあとに指定した列の属するテーブル名を指定します。
また、どのテーブルのどの列で結合するのかも条件として指定します。

SELECT 属するテーブル名.列名 属するテーブル名.列名 , …. FROM テーブル名,テーブル名 ;

/* テーブルの結合 */

SELECT 受注表.受注番号,顧客表.顧客名,
        商品表.商品名,受注表.受注個数,受注表.納品日
        FROM 受注表,顧客表,商品表
        WHERE 受注表.顧客コード = 顧客表.顧客コード
              AND 受注表.商品コード = 商品表.商品コード ;
/* 実行結果 */

受注番号 顧客名       商品名 受注個数 納品日
──── ────── ─── ──── ────
10001    KUROKIYA     JUICE  300      20010401
10002    ONSIDE       BEER   200      20010401
10003    KUSHINANA    BEER   150      20010401
10004    KUSHINANA    JUICE  80       20010402
10005    ONSIDE       WINE   110      20010402
10006    KUROKIYA     TEA    250      20010402
10007    FIRST HOUSE  JUICE  45       20010402
10008    KUSHINANA    BEER   135      20010403
10009    FIRST HOUSE  TEA    65       20010403
10010    KUROKIYA     BEER   290      20010403
10011    FIRST HOUSE  TEA    60       20010404
10012    KUSHINANA    BEER   175      20010404

これまで列の属するテーブルを明記してこなかったのは、参照する列が FROM 句で指定したテーブルに属しているのが明らかだったからです。

従って、FROM 句で複数のテーブルを参照する場合には、どのテーブルのどの列なのかということを明記しなければなりません。

しかし、全ての列名が異なる場合、たとえば受注表の顧客コードが顧客番号、受注表の商品コードが商品番号という名前であるような場合は、テーブル名を明記する必要はないですが、一般的にわかりやすさという観点から、きちんとテーブル名を明記することがよいとされているようです。

先のサンプルでは、DBMS はまず受注表の顧客コードと顧客コードを照らし合わせて、その組み合わせが TRUE を形成する時、すなわちその値が一致する時、その組み合わせから出力する値を選択します。

同時に受注表の商品コードと商品表の商品コードを照らし合わせて、その値が一致する時、出力する値を選択します。
そして、SELECT に続く列名についてのデータを表示します。

このように、外部キーを親キーに結び付けている結合を自然結合と言います。

しかし、SQL では設計されたリレーションシップによるデータの抽出だけでなく、データの内容に基づくリレーションシップによるデータの抽出も行えます。
たとえば、納品日が ’20010401′ の日に納品する商品名を抽出するには次のように SQL を書きます。

/* テーブルの結合 */

SELECT 受注表.納品日,商品表.商品名
        FROM 受注表,商品表
        WHERE 受注表.納品日 = '20010401'
        AND 受注表.商品コード = 商品表.商品コード ;
/* 実行結果 */

納品日   商品名
──── ───
20010401 JUICE
20010401 BEER
20010401 BEER

自己結合

自己結合とは同じテーブル同士の結合のことを言います。

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

テーブルの自己結合を行う際に問題なのは、テーブルの名前も列の名前も全て同じだという点です。
これを解決するために、テーブルに一時的な名前を付けます。

この一時的な名前のことをエイリアスと言い、範囲変数または相関変数とも呼びます。

商品表の単価が同じである商品名と単価を表示するには以下のSQLを実行します。

/* テーブルの結合 */

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

エイリアスはクエリの FROM 句に定義します。
テーブル名のあとにスペースを空け、エイリアスを指定すれば定義は完了です。

WHERE 句で、「A.商品コード <> B.商品コード」としているのは、これを指定しないと全ての商品の単価が表示されてしまうからです。

自己結合の最も一般的な用途は循環リレーションシップへの対応といわれています。

自分のテーブルに存在する列を外部キーとして指定するような、テーブルに循環型の外部キーが含まれる場合があるときに、自己結合はよく用いられます。

ここで説明用のテーブルを一つ定義しましょう。

次の従業員表では、上司列が従業員ナンバーを参照する外部キーになっています。どの社員も上司を持っていて、上司自身も社員であるため、従業員表に定義されるというものです。

社員と上司の組み合わせを参照する時は、次のように SQL を記述します。

従業員表

従業員ナンバー 従業員名 上司
135 INAGAKI 240
240 KIMURA NULL
184 KUSANAGI 135
235 KATORI 240
/* テーブルの結合 */

 SELECT A.従業員ナンバー,A.従業員名,B.上司名
        FROM 従業員表 A,従業員表 B
        WHERE A.上司 = B.従業員ナンバー ;
/* 実行結果 */

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

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


− 6 = 3

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>