【Oracle実験室】No.0069「自己結合」

No.0069(SQL)
自己結合

■環境

　　□OS：　Windows XP Professional SP3
　　□Oracle：　Oracle Database 11g R2 (Standard)
　　□OSログインユーザ：　ORA_DBAグループ
　　□Oracleユーザ：　dicdic(実験用ユーザー) → 実験環境構築スクリプト

次の手順を実行すると、下に掲げる結合の例題をすべて再現できます。
(このページのスクリプトで、基本的な実行環境ができていることが前提です)
※一般のdicdicユーザーの環境ではできません。下記手順を行ってください。

(手順)
　①下記ファイルをダウンロードします。
これ
　②SQL*Plusを起動します。
　③ダウンロードしたファイルをテキストエディタで開き、1～30行目を選択します。
　④SQL*Plusの画面でペースト(貼り付け)します。
　⑤このページで使用しているSQL文は こちら

■実験

　　0.　実験ユーザー「dicdic」でコネクトする。


SQL> conn dicdic/Oracle11g
接続されました。

SQL>

　　1.【自己結合をする場合の例】
　　「JOINTEST_SELF」表には、「BOSS」列があり、直属上司の番号が登録されている。
　　たとえばこの表で、三郎(BOSS=2)の上司は一郎(NAME_NO=2)である。
　　一長(BOSS=0)は社長であり、上司がいない。


SQL> Select * from JOINTEST_SELF;

   NAME_NO NAME       BOSS
---------- ---- ----------
         1 一長          0
         2 一郎          1
         3 二郎          2
         4 三郎          2
         5 四郎          2
         6 五郎          2

6行が選択されました。

SQL>

　　2.【内部結合の自己結合】
　　名前と上司の名前を併記する。
　　内部結合の場合、上司がいない社長(一長)は表示されない。


SQL> Select T1.NAME_NO,T1.NAME,T1.BOSS,T2.NAME
  2  From JOINTEST_SELF T1
  3  INNER JOIN JOINTEST_SELF T2
  4  ON T1.BOSS=T2.NAME_NO;

   NAME_NO NAME       BOSS NAME
---------- ---- ---------- ----
         2 一郎          1 一長
         3 二郎          2 一郎
         4 三郎          2 一郎
         5 四郎          2 一郎
         6 五郎          2 一郎

SQL>

　　3.【左外部結合の自己結合】
　　名前と上司の名前を併記する。
　　左外部結合の場合、全員の名前が表示される。(社長は上司の名前がない状態)


SQL> Select T1.NAME_NO,T1.NAME,T1.BOSS,T2.NAME
  2  From JOINTEST_SELF T1
  3  LEFT JOIN JOINTEST_SELF T2
  4  ON T1.BOSS=T2.NAME_NO;

   NAME_NO NAME       BOSS NAME
---------- ---- ---------- ----
         1 一長          0
         2 一郎          1 一長
         3 二郎          2 一郎
         4 三郎          2 一郎
         5 四郎          2 一郎
         6 五郎          2 一郎

6行が選択されました。

SQL>

　　4.【右外部結合の自己結合】
　　名前と上司の名前を併記する。
　　右外部結合の場合、右表から見ると、部下の一覧という格好になる。
　　右表から見て部下がいない人は左表の名前が空になる。


SQL> Select T1.NAME_NO,T1.NAME,T1.BOSS,T2.NAME
  2  From JOINTEST_SELF T1
  3  RIGHT JOIN JOINTEST_SELF T2
  4  ON T1.BOSS=T2.NAME_NO;

   NAME_NO NAME       BOSS NAME
---------- ---- ---------- ----
         2 一郎          1 一長
         3 二郎          2 一郎
         4 三郎          2 一郎
         5 四郎          2 一郎
         6 五郎          2 一郎
                           五郎
                           四郎
                           三郎
                           二郎

9行が選択されました。

SQL>

　　5.【完全外部結合の自己結合】
　　上司一覧と部下一覧を一緒にしたような形。


SQL> Select T1.NAME_NO,T1.NAME,T1.BOSS,T2.NAME
  2  From JOINTEST_SELF T1
  3  FULL JOIN JOINTEST_SELF T2
  4  ON T1.BOSS=T2.NAME_NO;

   NAME_NO NAME       BOSS NAME
---------- ---- ---------- ----
         2 一郎          1 一長
         6 五郎          2 一郎
         5 四郎          2 一郎
         4 三郎          2 一郎
         3 二郎          2 一郎
                           二郎
                           三郎
                           四郎
                           五郎
         1 一長          0

10行が選択されました。

SQL>

【ＰＲ】 Oracle実験室監修 『Oracleアーキテクチャ鳥瞰図』ＰＤＦ （Oracleのアーキテクチャを一目で俯瞰できるイラスト）

Home －実験室－ No.0069

ブログランキング