Oracle SQL 表の結合2(「INNER JOIN」、「LEFT JOIN」)

「その他・Tips」の「外部結合演算子 (+) とANSI規格について」のところで少し載せてはいますが、 OracleではWHERE句の中でテーブルのカラムを「=」で連結することで等結号 「=(+)」で連結することで外部結号ができます。

但し、この方法はOracleだけにしか使用できなくて、他のデータベース「MSSQL」「MySQL」「PostgreSQL」では 「INNER JOIN」「LEFT JOIN」を使って行います。 「JOIN」を使う方がANSIの規格にも合っていますし、他のデータベースを扱う場合にも慣れておくことは必要だと思います。
JOIN句を使う構文は以下の様になります。

SELECT <列名リスト> FROM <表名1>
  INNER JOIN(LEFT JOIN) <表名2>
  ON <結合の条件>
  [AND <追加の結合の条件>]

それでは「Oracle SQL 表の結合」の例を「INNER JOIN」「LEFT JOIN」で書き換えたものを示します。


等結合

複数の表のデータを参照し、一括したデータとして扱うことは重要で、これを実現する為に等結合の方法を使います。
等結合とは一言で言えば、複数の表を結合する為に共通した列をそれぞれの表に持ち、 それらが等しいデータ行を選択対象とするとなります。 等結合を簡単な販売管理のデータを用いて説明しますので、以下のデータの表が既に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_売上明細
  2  INNER JOIN TM_商品
  3  ON 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文で注目すべきはFROM句で主となるテーブルを宣言し、その後のINNER JOIN句のところで、 連結するテーブルであるTM_商品を宣言します。更に各々のテーブルの度のカラムで連結するかをON句で宣言します。 ここではTT_売上明細の商品コードとTM_商品の商品コードを"="で条件付けしています。

 SELECTは"*"で項目指定していますので、2個の表の全ての列を表示しますが、必要なデータのみ表示 させたい場合にはSELECT句において<表名>.<列名>として個別に列名を列挙することができます。

以下に例を示しますが、FROM句で表の別名を設定し、SELECT句でもその別名で列名の記述を行っています。
(別名を使い表名を簡略化してSELECT句を読みやすくしています。)

SQL> SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  2         TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  3  FROM TT_売上明細 TU
  4  INNER JOIN TM_商品 TS
  5  ON 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
  4  INNER JOIN TM_商品 TS
  5  ON 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_売上明細のみのデータをも表示させる為には次の外部結合と言う方法になります。


外部結合(LEFT JOIN)

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

SQL> SELECT TU.売上番号,TU.明細番号,TU.商品コード,
  2         TS.商品名,TS.売上単価,TU.売上数量,TS.売上単価 * TU.売上数量 AS 金額
  3  FROM TT_売上明細 TU 
  4  LEFT JOIN TM_商品 TS
  5  ON 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行が選択されました。

JOIN句が「LEFT JOIN」に変更されています。結果の表示をみると削除された商品コード8番の商品名、売上単価、金額がNULLの表示になっています。

主となるテーブルがFROM句の「TT_売上明細」であり従となるテーブル「TM_商品」を「LEFT JOIN」で結合することから左外部結合と呼ばれています。

この結合と反対の右結合「RIGHT JOIN」もあり、右側の表を優先することになります。 ただ、私は「RIGHT JOIN」をほとんど使用することは無く、ほぼ全て「LEFT JOIN」で行けるのではないかと思います。

外部結合のまとめとして得意先コード順に売上データを表示するSELECT文を以下に示します。 尚、商品コードが8番の"PRT-4001"は復活してあります。

SQL> SELECT TH.得意先コード,TC.得意先名,TH.売上番号,TH.売上日,
  2         TD.明細番号,TD.商品コード,TS.商品名,TS.売上単価,TD.売上数量,
  3         TS.売上単価 * TD.売上数量 AS 金額
  4  FROM TT_売上 TH
  5  INNER JOIN TT_売上明細 TD 
  6  ON TH.売上番号 = TD.売上番号
  7  LEFT JOIN TM_得意先 TC
  8  ON TH.得意先コード = TC.得意先コード
  9  LEFT JOIN TM_商品 TS
  10 ON TD.商品コード   = TS.商品コード
  11 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文字目に付けたりしています。


JOINのAND条件

INNER JOINでのON句で結合条件を指定しますが、更にその時の条件を追加したい場合にANDで条件を指定します。 直前のSQLでTT_売上明細の明細番号が「1」のデータのみを対象とする場合の例を示します。 (例としてはあまり意味がありませんが…)

SQL> SELECT TH.得意先コード,TC.得意先名,TH.売上番号,TH.売上日,
  2         TD.明細番号,TD.商品コード,TS.商品名,TS.売上単価,TD.売上数量,
  3         TS.売上単価 * TD.売上数量 AS 金額
  4  FROM TT_売上 TH
  5  INNER JOIN TT_売上明細 TD 
  6  ON TH.売上番号 = TD.売上番号
  7  AND TD.明細番号 = 1
  8  LEFT JOIN TM_得意先 TC
  9  ON TH.得意先コード = TC.得意先コード
  10 LEFT JOIN TM_商品 TS
  11 ON TD.商品コード   = TS.商品コード
  12 ORDER BY TH.得意先コード , TH.売上番号 , TD.明細番号;

得意先コード 得意先名             売上番号 売上日   明細番号 商品コード 商品名         売上単価  売上数量    金額
------------ -------------------- -------- -------- -------- ---------- ------------- --------- --------- -------
           1 (株)青木商事              1 02-11-01        1          1 PC-9001           98000         2  196000
           1 (株)青木商事              6 02-11-15        1          8 PRT-4001         180000         1  180000
           2 山本商店                    2 02-11-03        1          2 PC-9002          120000         1  120000
           3 ソフトランド                4 02-11-13        1          6 NOTE-1030        220000         1  220000
           4 (株)ソフトプラザ          3 02-11-04        1          3 PC-9003          190000         3  570000
           5 (株)福井商事              5 02-11-15        1          8 PRT-4001         180000         3  540000

6行が選択されました。

SQL 第2版 ゼロからはじめるデータベース操作【電子書籍】[ ミック ]

プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応)【電子書籍】[ アシスト教育部 ]




ページのトップへ戻る