Oracle SQL 表の結合２(「INNER JOIN」、「LEFT JOIN」)
「その他・Tips」の「外部結合演算子 (+) とANSI規格について」のところで少し載せてはいますが、
OracleではWHERE句の中でテーブルのカラムを「＝」で連結することで等結号
「＝（＋）」で連結することで外部結号ができます。
但し、この方法はOracleだけにしか使用できなくて、他のデータベース「MSSQL」「MySQL」「PostgreSQL」では
「INNER JOIN」「LEFT JOIN」を使って行います。
「JOIN」を使う方がANSIの規格にも合っていますし、他のデータベースを扱う場合にも慣れておくことは必要だと思います。
JOIN句を使う構文は以下の様になります。
SELECT <列名リスト> FROM <表名１> INNER JOIN(LEFT JOIN) <表名２> ON <結合の条件> [AND <追加の結合の条件>]
それでは「Oracle SQL 表の結合」の例を「INNER JOIN」「LEFT JOIN」で書き換えたものを示します。
- 「INNER JOIN」での結合
- 「INNER JOIN」「LEFT JOIN」での結合
- 追加の結合の条件
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
等結合
複数の表のデータを参照し、一括したデータとして扱うことは重要で、これを実現する為に等結合の方法を使います。
等結合とは一言で言えば、複数の表を結合する為に共通した列をそれぞれの表に持ち、
それらが等しいデータ行を選択対象とするとなります。
等結合を簡単な販売管理のデータを用いて説明しますので、以下のデータの表が既にOracle上に存在することとします。
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
今回の表の中から、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_商品のどちらかの行が存在しない場合にはその行は表示されないことになります。
もし仮に上の例で、商品コードが８番の"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文を以下に示します。
尚、商品コードが８番の"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_売上明細の明細番号が「１」のデータのみを対象とする場合の例を示します。
（例としてはあまり意味がありませんが…）
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行が選択されました。
