【Oracle実験室】No.0067「表の結合の種類と概要、その基本パターン演習」

No.0067(SQL)
表の結合の種類と概要、その基本パターン演習

■環境

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

2つ以上の表(ビュー)を結合する問合せ。

SELECT [①表示したい列を列挙] FROM [②左表(FROM表)] [④結合方式] JOIN [③右表(JOIN表)] ON [⑤結合条件];

  ①表示したい列 ･･･列名は、[テーブル名.列名]で記述する。
     ただし、④の結合条件でUSINGを使用する場合、結合する列はテーブル名修飾しない。

  ②右表と③左表 ･････結合したい2表を、左表と右表に割り当てる。どちらをどちらに割り当ててもよいが、
     それによって④の結合方式の書き方が変わるので注意する。

  ④結合方式 ･･･････ inner、left outer、right outer、crossなどが入る。結合の肝。

  ⑤結合条件 ･･･････結合する列を指定する。基本的には以下、2通りの記述方法がある。
     (ON句を使う)　ON 左表名.結合列名=右表名.結合列名
     (USINGを使う)　USING(結合列名)
     ただし、USINGの場合、左表と右表で列名と型がバッチリ一致している必要がある。

結合方式	イメージ	AccessのGUI表現	SQLServerのGUI表現
内部結合
左外部結合
右外部結合
完全外部結合

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

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

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


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

SQL>

　　1.【内部結合の例①】


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   INNER JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女

8行が選択されました。

SQL>

　　2.【内部結合の例②】
　　「INNER」を略しても内部結合として解釈される。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女

8行が選択されました。

SQL>

　　3.【内部結合の例③】
　　ON句の代わりにUSINGを使用してもよい。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   JOIN JOINTEST_GENDER
  7   USING(GEND_CODE);

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女

8行が選択されました。

SQL>

　　4.【左外部結合の例①】


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   LEFT OUTER JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜

9行が選択されました。

SQL>

　　5.【左外部結合の例②】
　　「OUTER」を略しても外部結合として解釈される。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   LEFT JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜

9行が選択されました。

SQL>

　　6.【左外部結合の例③】
　　ON句の代わりにUSINGを使用してもよい。
　　①､②と若干違うことに注意する。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   LEFT JOIN JOINTEST_GENDER
  7   USING(GEND_CODE);

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜          9

9行が選択されました。

SQL>

　　7.【右外部結合の例①】


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   RIGHT OUTER JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
                         3 犬

9行が選択されました。

SQL>

　　8.【右外部結合の例②】
　　「OUTER」を略しても外部結合として解釈される。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   RIGHT JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
                         3 犬

9行が選択されました。

SQL>

　　9.【右外部結合の例③】
　　ON句の代わりにUSINGを使用してもよい。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   RIGHT JOIN JOINTEST_GENDER
  7   USING(GEND_CODE);

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
                         3 犬

9行が選択されました。

SQL>

　　10.【完全外部結合の例①】


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   FULL OUTER JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜
                         3 犬

10行が選択されました。

SQL>

　　11.【完全外部結合の例②】
　　「OUTER」を略しても外部結合として解釈される。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   FULL JOIN JOINTEST_GENDER
  7   ON JOINTEST_NAME.GEND_CODE=JOINTEST_GENDER.GEND_CODE;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜
                         3 犬

10行が選択されました。

SQL>

　　12.【完全外部結合の例③】
　　ON句の代わりにUSINGを使用してもよい。
　　①､②と若干違うことに注意する。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   FULL JOIN JOINTEST_GENDER
  7   USING(GEND_CODE);

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜          9
                         3 犬

10行が選択されました。

SQL>

　　13.【自然結合の例①】
　　「NATURAL JOIN」を利用すると、もっとも適切と思われる結合列を勝手に拾ってくれる。
　　「NATURAL JOIN」の場合、結合条件を書いてはならない
　　下は、内部結合で自然結合を利用している例。


SQL> Select     NAME_NO,
  2             NAME,
  3             GEND_CODE,
  4             GENDER
  5   from JOINTEST_NAME
  6   NATURAL JOIN JOINTEST_GENDER;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女

8行が選択されました。

SQL>

　　14.【自然結合の例②】
　　「NATURAL JOIN」を利用すると、もっとも適切と思われる結合列を勝手に拾ってくれる。
　　「NATURAL JOIN」の場合、結合条件を書いてはならない
　　下は、左外部結合で自然結合を利用している例。


SQL> Select     NAME_NO,
  2             NAME,
  3             GEND_CODE,
  4             GENDER
  5   from JOINTEST_NAME
  6   NATURAL LEFT JOIN JOINTEST_GENDER;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜          9

9行が選択されました。

SQL>

　　15.【自然結合の例③】
　　「NATURAL JOIN」を利用すると、もっとも適切と思われる結合列を勝手に拾ってくれる。
　　「NATURAL JOIN」の場合、結合条件を書いてはならない
　　下は、右外部結合で自然結合を利用している例。


SQL> Select     NAME_NO,
  2             NAME,
  3             GEND_CODE,
  4             GENDER
  5   from JOINTEST_NAME
  6   NATURAL RIGHT JOIN JOINTEST_GENDER;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
                         3 犬

9行が選択されました。

SQL>

　　16.【単純結合(クロス結合)の例①】
　　右表のすべての行が、左表のすべての行と結合するので、結合条件がない。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from JOINTEST_NAME
  6   CROSS JOIN JOINTEST_GENDER;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          1 女
         2 Ｂ郎          1 女
         3 Ｃ郎          1 女
         4 Ｄ郎          1 女
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜          1 女
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          2 男
         6 Ｂ子          2 男
         7 Ｃ子          2 男
         8 Ｄ子          2 男
         9 Ａ釜          2 男
         1 Ａ郎          3 犬
         2 Ｂ郎          3 犬
         3 Ｃ郎          3 犬
         4 Ｄ郎          3 犬
         5 Ａ子          3 犬
         6 Ｂ子          3 犬
         7 Ｃ子          3 犬
         8 Ｄ子          3 犬
         9 Ａ釜          3 犬

27行が選択されました。

SQL>

　　17.【単純結合(クロス結合)の例②】
　　「CROSS JOIN」を明記せず、結合条件もなければ直積になる。


SQL> Select     JOINTEST_NAME.NAME_NO,
  2             JOINTEST_NAME.NAME,
  3             JOINTEST_GENDER.GEND_CODE,
  4             JOINTEST_GENDER.GENDER
  5   from      JOINTEST_NAME,
  6             JOINTEST_GENDER;

   NAME_NO NAME  GEND_CODE GE
---------- ---- ---------- --
         1 Ａ郎          1 女
         2 Ｂ郎          1 女
         3 Ｃ郎          1 女
         4 Ｄ郎          1 女
         5 Ａ子          1 女
         6 Ｂ子          1 女
         7 Ｃ子          1 女
         8 Ｄ子          1 女
         9 Ａ釜          1 女
         1 Ａ郎          2 男
         2 Ｂ郎          2 男
         3 Ｃ郎          2 男
         4 Ｄ郎          2 男
         5 Ａ子          2 男
         6 Ｂ子          2 男
         7 Ｃ子          2 男
         8 Ｄ子          2 男
         9 Ａ釜          2 男
         1 Ａ郎          3 犬
         2 Ｂ郎          3 犬
         3 Ｃ郎          3 犬
         4 Ｄ郎          3 犬
         5 Ａ子          3 犬
         6 Ｂ子          3 犬
         7 Ｃ子          3 犬
         8 Ｄ子          3 犬
         9 Ａ釜          3 犬

27行が選択されました。

SQL>

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

Home －実験室－ No.0067

ブログランキング