Oracle SQL 表の結合

これまでは1個の表についてのいろいろな検索方法について説明してきましたが、現実問題として
1個の表だけでは処理できないことが出てきます。2個以上の表から必要とする情報を組み合わせて
1個のデータとして検索結果を表示することが、表を結合することで可能になります。
 一般的に表はデータの性格毎に別々の表として分けておくべきであり、どの様に別々の表とするのかは
システム構築の方法論とも絡んできますのでここでは述べないでおきます。(私の力ではシステム構築論
的なものは無理かもしれませんので、そちらの本なりを参照されてください)


おすすめ書籍



クロス結合

クロス結合を説明する為に以下の様に例としての表を作成します。内容的には全く同じものです。

表<TT_TEST1>
CD:VARCHAR2(3) DATA:NUMBER(3)
001 1
002 2
003 3
004 4
005 5
006 6
007 7
008 8
009 9
表<TT_TEST2>
CD:VARCHAR2(3) DATA:NUMBER(3)
001 1
002 2
003 3
004 4
005 5
006 6
007 7
008 8
009 9

クロス結合とは、2個の表を単純に結合することであり、一方の表の1行について他方の表の全ての行を
対応付けた行を返すことになります。言葉でいってもはっきりしないので以下の例を見れば別に難しいことでは
ないと思います。FROM句に2個の表を指定し全ての列を選択する様にSELECT文を指定しています。
クロス結合は別名、直積結合とも言われ、この例のSELECT文の結果が返す行数は一方の表の行数掛ける
他方の表の行数になります。

SQL> SELECT * FROM TT_TEST1 , TT_TEST2;

CD        DATA CD        DATA
--- ---------- --- ----------
001          1 001          1
002          2 001          1
003          3 001          1
004          4 001          1
005          5 001          1
006          6 001          1
007          7 001          1
008          8 001          1
009          9 001          1
001          1 002          2
002          2 002          2

CD        DATA CD        DATA
--- ---------- --- ----------
003          3 002          2
004          4 002          2
005          5 002          2
006          6 002          2
007          7 002          2
008          8 002          2
009          9 002          2
001          1 003          3
002          2 003          3
003          3 003          3
004          4 003          3

...
...

クロス結合は今までいろいろなシステム上でほとんど利用したことが無く、利用例を示す
にも九九表ぐらいしか思い当たりませんでした。

SQL> SELECT SUBSTR(TO_CHAR(T2.DATA) || ' * ' || TO_CHAR(T1.DATA) || ' = ',1,8) AS MUL,
  2         T1.DATA * T2.DATA AS 値
  3  FROM TT_TEST1 T1 , TT_TEST2 T2;

MUL                      値
---------------- ----------
1 * 1 =                   1
1 * 2 =                   2
1 * 3 =                   3
1 * 4 =                   4
1 * 5 =                   5
1 * 6 =                   6
1 * 7 =                   7
1 * 8 =                   8
1 * 9 =                   9
2 * 1 =                   2
2 * 2 =                   4

MUL                      値
---------------- ----------
2 * 3 =                   6
2 * 4 =                   8
2 * 5 =                  10
2 * 6 =                  12
2 * 7 =                  14
2 * 8 =                  16
2 * 9 =                  18
3 * 1 =                   3
3 * 2 =                   6

...
...

等結合

複数の表のデータを参照し、一括したデータとして扱うことは重要で、これを実現する為に等結合の方法を使います。
等結合とは一言で言えば、複数の表を結合する為に共通した列をそれぞれの表に持ち、それらが等しいデータ行を
選択対象とするとなります。等結合を簡単な販売管理のデータを用いて説明しますので、以下のデータの表が既に
Oracle上に存在することとします。

TM_商品
商品コード 商品名 商品区分 仕入単価 売上単価
1 PC-9001 デスクトップパソコン 65000 98000
2 PC-9002 デスクトップパソコン 95000 120000
3 PC-9003 デスクトップパソコン 150000 190000
4 NOTE-1010 ノートパソコン 125000 188000
5 NOTE-1020 ノートパソコン 145000 200000
6 NOTE-1030 ノートパソコン 155000 220000
7 PRT-3001 プリンタ 45000 88000
8 PRT-4001 プリンタ 115000 180000
9 CRT-1001 ディスプレイ 45000 78000
10 CRT-2001 ディスプレイ 55000 98000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
TM_担当者
担当者コード 上司コード 担当者名 生年月日 性別
1   斎藤 1960/04/05 1
2   山田 1957/10/15 1
3   田中 1962/07/08 2
4 1 島田 1967/05/05 1
5 1 鈴木 1970/06/04 1
6 1 田村 1975/08/01 2
7 2 山下 1972/03/18 1
8 2 山村 1976/09/18 2
9 3 多田 1978/12/10 2
TM_得意先
得意先コード 得意先名 住所 電話番号 請求締日
1 (株)青木商事 福井市大手1-1-1 0776-22-1111 20
2 山本商店 金沢市文京2-1-1 0762-22-2222 20
3 ソフトランド 富山市大手3-3-3 0764-33-3333 25
4 (株)ソフトプラザ 富山市春日4-3-3 0764-44-4444 25
5 (株)福井商事 福井市春日5-1-1 0776-55-0000 99
TT_売上
売上番号 売上日 得意先コード 担当者コード
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
TT_売上明細
売上番号 明細番号 商品コード 売上数量
1 1 1 2
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1

今回の表の中から、TT_売上明細とTM_商品で等結合を行う場合、商品コードが共通の列となりこれが結合の条件となります。 以下に等結合のSELECT文の例を示します。

SQL> SELECT * FROM TT_売上明細 , TM_商品
  2  WHERE TT_売上明細.商品コード = TM_商品.商品コード;

 売上番号  明細番号 商品コード  売上数量 商品コード 商品名      商品区分               仕入単価   売上単価
--------- --------- ---------- --------- ---------- ----------- -------------------- ---------- ----------
        1         1          1         2          1 PC-9001     デスクトップパソコン      65000      98000
        1         2          4         1          4 NOTE-1010   ノートパソコン           125000     188000
        2         1          2         1          2 PC-9002     デスクトップパソコン      95000     120000
        2         2          5         2          5 NOTE-1020   ノートパソコン           145000     200000
        2         3         11         1         11 HUB-A001    ネットワーク              20000      40000
        3         1          3         3          3 PC-9003     デスクトップパソコン     150000     190000
        4         1          6         1          6 NOTE-1030   ノートパソコン           155000     220000
        4         2          7         2          7 PRT-3001    プリンタ                  45000      88000
        5         1          8         3          8 PRT-4001    プリンタ                 115000     180000
        5         2          9         2          9 CRT-1001    ディスプレイ              45000      78000
        6         1          8         1          8 PRT-4001    プリンタ                 115000     180000

 売上番号  明細番号 商品コード  売上数量 商品コード 商品名      商品区分               仕入単価   売上単価
--------- --------- ---------- --------- ---------- ----------- -------------------- ---------- ----------
        6         2          9         1          9 CRT-1001    ディスプレイ              45000      78000
        6         3         10         1         10 CRT-2001    ディスプレイ              55000      98000

13行が選択されました。

TT_売上明細の商品コードに対応するTM_商品のデータが、TT_売上明細の右側に1行のデータとして表示
されています。このSELECT文で注目すべきはWHERE句のところで、TT_売上明細の商品コードとTM_商品の
商品コードを"="で条件付けしています。

 SELECTは"*"で項目指定していますので、2個の表の全ての列を表示しますが、必要なデータのみ表示
させたい場合にはSELECT句において<表名>.<列名>として個別に列名を列挙することができます。
以下に例を示しますが、FROM句で表の別名を設定し、SELECT句でもその別名で列名の記述を行っています。
(別名を使い表名を簡略化してSELECT句を読みやすくしています。)

SQL> SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  2         TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  3  FROM TT_売上明細 TU , TM_商品 TS
  4  WHERE TU.商品コード = TS.商品コード;

  売上番号   明細番号 商品コード 商品名         売上単価   売上数量       金額
---------- ---------- ---------- ------------ ---------- ---------- ----------
         1          1          1 PC-9001           98000          2     196000
         1          2          4 NOTE-1010        188000          1     188000
         2          1          2 PC-9002          120000          1     120000
         2          2          5 NOTE-1020        200000          2     400000
         2          3         11 HUB-A001          40000          1      40000
         3          1          3 PC-9003          190000          3     570000
         4          1          6 NOTE-1030        220000          1     220000
         4          2          7 PRT-3001          88000          2     176000
         5          1          8 PRT-4001         180000          3     540000
         5          2          9 CRT-1001          78000          2     156000
         6          1          8 PRT-4001         180000          1     180000

  売上番号   明細番号 商品コード 商品名         売上単価   売上数量       金額
---------- ---------- ---------- ------------ ---------- ---------- ----------
         6          2          9 CRT-1001          78000          1      78000
         6          3         10 CRT-2001          98000          1      98000

13行が選択されました。

TT_売上明細の売上番号、明細番号はTM_商品には存在しない列名なのでSELECT句の中で"TU."の指定は
必要はありませんが、商品コードは両方の表に存在しますのでどちらから列データを表示するかを
明示的に指定する必要があります。この例で言えば、主たる表はTT_売上明細になる為、TT_売上明細
からの表示になります。

 等結合の場合は1対1に表データが存在するもののみ表示します。つまり上の例で言えば、TT_売上明細
もしくはTM_商品のどちらかの行が存在しない場合にはその行は表示されないことになります。
もし仮に上の例で、商品コードが8番の"PRT-4001"を削除するとどうなるでしょうか。その結果は
以下のSELECT文に示します。

SQL> SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  2         TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  3  FROM TT_売上明細 TU , TM_商品 TS
  4  WHERE TU.商品コード = TS.商品コード;

  売上番号   明細番号 商品コード 商品名                                     売上単価   売上数量       金額
---------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
         1          1          1 PC-9001                                       98000          2     196000
         1          2          4 NOTE-1010                                    188000          1     188000
         2          1          2 PC-9002                                      120000          1     120000
         2          2          5 NOTE-1020                                    200000          2     400000
         2          3         11 HUB-A001                                      40000          1      40000
         3          1          3 PC-9003                                      190000          3     570000
         4          1          6 NOTE-1030                                    220000          1     220000
         4          2          7 PRT-3001                                      88000          2     176000
         5          2          9 CRT-1001                                      78000          2     156000
         6          2          9 CRT-1001                                      78000          1      78000
         6          3         10 CRT-2001                                      98000          1      98000

11行が選択されました。

これではTT_売上明細にはデータが存在するのに表示されないという不都合が発生します。
(売上明細には数量しか持っていない為、商品マスタが無ければ金額が計算できないと言う更なる問題が
 発生してはきます。)
TT_売上明細のみのデータをも表示させる為には次の外部結合と言う方法になります。


外部結合

等結合の最後の例で出てきたTT_売上明細の商品コードは存在するが、TM_商品にはその商品コードが
存在しない時にもTT_売上明細を表示する様にSELECT文を少し変更します。

SQL> SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  2         TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  3  FROM TT_売上明細 TU , TM_商品 TS
  4  WHERE TU.商品コード = TS.商品コード(+);

  売上番号   明細番号 商品コード 商品名                                     売上単価   売上数量       金額
---------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
         1          1          1 PC-9001                                       98000          2     196000
         1          2          4 NOTE-1010                                    188000          1     188000
         2          1          2 PC-9002                                      120000          1     120000
         2          2          5 NOTE-1020                                    200000          2     400000
         2          3         11 HUB-A001                                      40000          1      40000
         3          1          3 PC-9003                                      190000          3     570000
         4          1          6 NOTE-1030                                    220000          1     220000
         4          2          7 PRT-3001                                      88000          2     176000
         5          1          8                                                              3
         5          2          9 CRT-1001                                      78000          2     156000
         6          1          8                                                              1

  売上番号   明細番号 商品コード 商品名                                     売上単価   売上数量       金額
---------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
         6          2          9 CRT-1001                                      78000          1      78000
         6          3         10 CRT-2001                                      98000          1      98000

13行が選択されました。

WHERE句の"TS.商品コード"の最後に"(+)"が追加されています。結果の表示をみると削除された商品コード
8番の商品名、売上単価、金額がNULLの表示になっています。

WHERE句の条件の左側の表を優先することから左外部結合と呼ばれています。
この結合と反対の右結合もあり、右側の表を優先することになります。

外部結合のまとめとして得意先コード順に売上データを表示するSELECT文を以下に示します。

SQL> SELECT TH.得意先コード,TC.得意先名,TH.売上番号,TH.売上日,
  2         TD.明細番号,TD.商品コード,TS.商品名,TS.売上単価,TD.売上数量,
  3         TS.売上単価 * TD.売上数量 AS 金額
  4  FROM TT_売上 TH , TT_売上明細 TD , TM_得意先 TC , TM_商品 TS
  5  WHERE TH.売上番号     = TD.売上番号
  6  AND   TH.得意先コード = TC.得意先コード(+)
  7  AND   TD.商品コード   = TS.商品コード(+)
  8  ORDER BY TH.得意先コード , TH.売上番号 , TD.明細番号;

得意先コード 得意先名             売上番号 売上日   明細番号 商品コード 商品名         売上単価  売上数量    金額
------------ -------------------- -------- -------- -------- ---------- ------------- --------- --------- -------
           1 (株)青木商事              1 02-11-01        1          1 PC-9001           98000         2  196000
           1 (株)青木商事              1 02-11-01        2          4 NOTE-1010        188000         1  188000
           1 (株)青木商事              6 02-11-15        1          8 PRT-4001         180000         1  180000
           1 (株)青木商事              6 02-11-15        2          9 CRT-1001          78000         1   78000
           1 (株)青木商事              6 02-11-15        3         10 CRT-2001          98000         1   98000
           2 山本商店                    2 02-11-03        1          2 PC-9002          120000         1  120000
           2 山本商店                    2 02-11-03        2          5 NOTE-1020        200000         2  400000
           2 山本商店                    2 02-11-03        3         11 HUB-A001          40000         1   40000
           3 ソフトランド                4 02-11-13        1          6 NOTE-1030        220000         1  220000
           3 ソフトランド                4 02-11-13        2          7 PRT-3001          88000         2  176000
           4 (株)ソフトプラザ          3 02-11-04        1          3 PC-9003          190000         3  570000

得意先コード 得意先名             売上番号 売上日   明細番号 商品コード 商品名         売上単価  売上数量    金額
------------ -------------------- -------- -------- -------- ---------- ------------- --------- --------- -------
           5 (株)福井商事              5 02-11-15        1          8 PRT-4001         180000         3  540000
           5 (株)福井商事              5 02-11-15        2          9 CRT-1001          78000         2  156000

13行が選択されました。

TT_売上とTT_売上明細は売上番号により等結合をし、さらにTT_売上の得意先コードとTM_得意先の
得意先コードとは左外部結合を行い、TT_売上の商品コードとTM_商品の商品コードと左外部結合を
行っています。
(よく他の参考書等では表の別名は1文字でA,Bとして説明されていますが、実際にはA,Bなどの様に
 1文字では意味がわからなくなります。私自身の方法ですがなるべく2文字ぐらいとして先頭は
表の意味で"T"としその後でTT_売上ならばHEADERの"H"を付けたり、TT_売上明細であれば
DETAILの"D"を2文字目に付けたりしています)

自己結合

表の結合では特殊な方法で、自分自身を別の表の様に考えて結合を行うことを言います。
担当者マスタ(TM_担当者)では上司コードを用いて自分がどの上司の部下であるかを関係付けています。
以下のSELECT文では、担当者毎の上司の名前を表示させています。尚、自分自身が上司ある場合は
上司コードはNULLになっているため、SELECT句の2番目のところでNVL関数を用いて処理を分けています。

SQL> SELECT T1.担当者コード,NVL(T2.担当者名,'主任') 上司の名前,T1.担当者名 
  2  FROM TM_担当者 T1,TM_担当者 T2
  3  WHERE T1.上司コード = T2.担当者コード(+);

担当者コード 上司の名前           担当者名
------------ -------------------- --------------------
           1 主任                 斎藤
           2 主任                 山田
           3 主任                 田中
           4 斎藤                 島田
           5 斎藤                 鈴木
           6 斎藤                 田村
           7 山田                 山下
           8 山田                 山村
           9 田中                 多田

9行が選択されました。



■関連記事
表の結合2(JOIN)・等結合「INNER JOIN」での結合
表の結合2(JOIN)・外部結合「INNER JOIN」「LEFT JOIN」での結合
表の結合2(JOIN)・JOINのAND条件


ページのトップへ戻る