Oracle SQL 表の結合
これまでは1個の表についてのいろいろな検索方法について説明してきましたが、現実問題として
1個の表だけでは処理できないことが出てきます。2個以上の表から必要とする情報を組み合わせて
1個のデータとして検索結果を表示することが、表を結合することで可能になります。
一般的に表はデータの性格毎に別々の表として分けておくべきであり、どの様に別々の表とするのかは
システム構築の方法論とも絡んできますのでここでは述べないでおきます。（私の力ではシステム構築論
的なものは無理かもしれませんので、そちらの本なりを参照されてください）
- 直積結合
- 同じデータ列を結合条件として複数の表からデータ行を検索
- 等結合をさらに拡張した結合方法
- 自分自身への結合
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
クロス結合
クロス結合を説明する為に以下の様に例としての表を作成します。内容的には全く同じものです。
|
|
|||||||||||||||||||||||||||||||||||||||||||||
クロス結合とは、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上に存在することとします。
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
今回の表の中から、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_商品のどちらかの行が存在しない場合にはその行は表示されないことになります。
もし仮に上の例で、商品コードが８番の"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行が選択されました。
■関連記事
⇒表の結合２(JOIN)・等結合「INNER JOIN」での結合⇒表の結合２(JOIN)・外部結合「INNER JOIN」「LEFT JOIN」での結合
⇒表の結合２(JOIN)・JOINのAND条件
