トップ > SQL入門 > 18章
18章 結合(基本構文)
本章はSELECT文の中でも、一番重い部分である結合についてとなります。

今まで学んできたSELECT文は1つの表からデータを取得する方法となりましたが、
今回の結合という機能を使用すると複数の表からデータを取得することが出来るようになります。

表の結合
表の結合
結合の方法ですが、結合するためには各表で結合する列が必要となります。
例えば、EMPLOYEES表とDEPARTMENTS表を結合する場合、
どの列で結合するのがよいのでしょうか。

各表にはDEPT_ID列が存在します。DEPT_ID列の値は、
各表同じ意味を持った値が格納されているので、この列が結合に適しています。
結合列で同じ値の行で結合し、各表の行と行を関連付けし、結果を表示します。

では構文を見ていきましょう。結合はANSI規格のものとOracle独自のものがあります。
今回はANSI規格であれば別のRDBMSでも使用できるので、ANSI構文を学んでいきますが、
試験対策の為、Oracle独自の構文も簡単に確認していきます。

結合構文
自然結合 結合列を自動的に選択し結合
USING句結合 結合列を手動で指定し結合
ON句結合 結合列を手動で指定し結合
CROSS JOIN結合 デカルト積で結合

USING句結合とON句結合の説明文は同じ内容となっています。
詳細は後程、書いていきたいと思います。

ではまずは自然結合の構文です。

[構文] 自然結合
SELECT 列名, 列名…
FROM 表名1
NATURAL JOIN 表名2;

自然結合の構文で結合を行う場合は、FROM句に1つ目の表、
NATURAL JOIN句に2つ目の表を指定します。
結合する列は特に指定する必要はありません。

  SQL> SELECT first_name ,dept_name
    2  FROM employees NATURAL JOIN departments;

  FIRST_NAME                     DEPT_NAME
  ------------------------------ ------------------------------
  Sato                           consulting
  Yamamoto                       Human Resources
  Watanabe                       Human Resources
  Watanabe                       Human Resources
  Kataoka                        Engineering
  Harada                         Engineering
  Koda                           Engineering
  Saito                          Engineering
  Akagi                          sales
  Sato                           sales
  Tao                            sales
  Kaneko                         sales
  Totsuka                        sales
  Kato                           consulting
  Sinagawa                       consulting
  Kuroda                         Engineering
  Nakamura                       sales
  Kamata                         Human Resources

  18 rows selected.


上記実行例では、EMPLOYEES表とDEPARTMENTS表を結合しています。
結合列はDEPT_ID列となります。自然結合では各表の同じ列名で結合を行います。
各DEPT_IDが同じ値で結合をしています。

自然結合の特徴は結合する列を自動的に決めてくれます。
今回はemployees,departments表にある列でdept_idという列で結合を行っています。

ただし、結合する列名が各表で同じでないといけません。
違う場合はエラーになります。また同じ列名が複数存在する場合は、

複数列で同じ値の組み合わせで結合を行います。
1つの列で結合を行いたい場合は、自然結合ではない方法で結合する必要があります。

  SQL> desc employees
   Name                          Null?    Type
   ----------------------------- -------- ----------------------------
   EMP_ID                        NOT NULL NUMBER(10)
   FIRST_NAME                             VARCHAR2(30)
   LAST_NAME                              VARCHAR2(30)
   ADDRESS                                VARCHAR2(100)
   TEL                                    VARCHAR2(12)
   SALARY                                 NUMBER(30)
   HIRE_DATE                              DATE
   MANAGER_ID                             NUMBER(10)
   DEPT_ID                                NUMBER(10)


  SQL> desc departments
   Name                          Null?    Type
   ----------------------------- -------- ----------------------------
   DEPT_ID                       NOT NULL NUMBER(10)
   DEPT_NAME                              VARCHAR2(30)
   LOC_ID                                 NUMBER(10)


上記は各表の列情報ですが、dept_idは両方にあり、その中の値も同じ値となっています。

自然結合は結合する列を自動的に決める構文でしたが、
結合する列を指定したいという場合は、USING句を使用した結合を行います。

[構文] USING句を使用した結合
 SELECT 列名, 列名 …
 FROM 表名1
 JOIN 表名2
 USING ( 列名 );

上記はUSING句を使用した結合構文です。USINGに結合したい列名を指定します。

  SQL> SELECT first_name ,dept_name
    2  FROM employees
    3  JOIN departments
    4  USING (dept_id);

  FIRST_NAME                     DEPT_NAME
  ------------------------------ ------------------------------
  Sato                           consulting
  Yamamoto                       Human Resources
  Watanabe                       Human Resources
  Watanabe                       Human Resources
  Kataoka                        Engineering
  Harada                         Engineering
  Koda                           Engineering
  Saito                          Engineering
  Akagi                          sales
  Sato                           sales
  Tao                            sales
  Kaneko                         sales
  Totsuka                        sales
  Kato                           consulting
  Sinagawa                       consulting
  Kuroda                         Engineering
  Nakamura                       sales
  Kamata                         Human Resources

  18 rows selected.


明示的に結合する列名を指定しています。
実行結果は自然結合と同じ結果となっていることが分かるかと思います。
構文の違いだけで取得する結果は同じですね。

ではここまで、自然結合とUSING句を使用した結合構文を紹介しました。
次はON句を使用した結合構文です。
先ほどと同様で構文だけの違いとなり、
ON句を使用してもUSING句を使用しても同じ結果が得られます。

では構文を確認していきます。

[構文] ON句を使用した結合
 SELECT 列名, 列名 …
 FROM 表名1
 JOIN 表名2
 ON 表名1.列名 = 表名2.列名;

結合を行う場合は、FROM句に1つ目の表、JOIN句に2つ目の表を指定します。
またONに結合する列名を指定します。
ONに指定する表は必ず表名を指定します。

USING句と違う点は結合する列名を各表ごとに指定できる点です。
つまりUSING句では構文上、結合列に使用する列名は同じである必要がありましたが、

ON句は各表で異なる列名を指定しても結合が可能です。
ただし、結合する列は同じデータ型、また同じ値の種類である必要があります。
では実行例をみていきましょう。

  SQL> SELECT first_name,dept_name
    2  FROM employees
    3  JOIN departments
    4  ON employees.dept_id = departments.dept_id;

  FIRST_NAME                     DEPT_NAME
  ------------------------------ ------------------------------
  Sato                           consulting
  Yamamoto                       Human Resources
  Watanabe                       Human Resources
  Watanabe                       Human Resources
  Kataoka                        Engineering
  Harada                         Engineering
  Koda                           Engineering
  Saito                          Engineering
  Akagi                          sales
  Sato                           sales
  Tao                            sales
  Kaneko                         sales
  Totsuka                        sales
  Kato                           consulting
  Sinagawa                       consulting
  Kuroda                         Engineering
  Nakamura                       sales
  Kamata                         Human Resources

  18 rows selected.


ON句を使用した実行例から、NATURAL JOIN,USING句を使用した結合と
同じ結果が表示されました。構文が違うだけで結果は同じとなります。

では、次の実行例です。

  SQL> SELECT employees.first_name,employees.dept_id,departments.dept_name
    2  FROM employees
    3  JOIN departments
    4  ON employees.dept_id = departments.dept_id;

  FIRST_NAME                        DEPT_ID DEPT_NAME
  ------------------------------ ---------- ------------------------------
  Sato                                   20 consulting
  Yamamoto                               10 Human Resources
  Watanabe                               10 Human Resources
  Watanabe                               50 Human Resources
  Kataoka                                40 Engineering
  Harada                                 40 Engineering
  Koda                                   40 Engineering
  Saito                                  40 Engineering
  Akagi                                  30 sales
  Sato                                   30 sales
  Tao                                    30 sales
  Kaneko                                 30 sales
  Totsuka                                30 sales
  Kato                                   20 consulting
  Sinagawa                               20 consulting
  Kuroda                                 40 Engineering
  Nakamura                               30 sales
  Kamata                                 50 Human Resources

  18 rows selected.


今回の実行例はSELECT句の列名が表名.列名という形で指定されています。
SELECTで指定しているdept_idはemployees表にもあり、departments表にもあります。

そのため、表名を修飾しなかった場合、
どっちの表から取り出して良いかが分からないため、必ず表名を修飾する必要があります。

以下実行例が、dept_idに表名を修飾しなかった場合です。

  SQL> SELECT employees.first_name,dept_id,departments.dept_name
    2  FROM employees
    3  JOIN departments
    4  ON employees.dept_id = departments.dept_id;
  SELECT employees.first_name,dept_id,departments.dept_name
                              *
  ERROR at line 1:
  ORA-00918: column ambiguously defined


上記の様にemployees表のdept_idかdepartments表のdept_idなのかが
分からないためエラーになっています。

また表名を修飾することにより、データを取得する速度が向上するメリットも
ありますので表名を付けることをお勧めします。

では次は表の別名の定義方法です。以前列の別名を定義する方法を学びましたが、
表の別名も定義することができます。
表別名を定義することにより、先ほどの表名の修飾が楽になります。

  SQL> SELECT e.first_name,e.dept_id,d.dept_name
    2  FROM employees e
    3  JOIN departments d
    4  ON e.dept_id = d.dept_id;
    

表別名を定義するには表名の後ろに別名を定義するだけとなります。
今回ではemployeesの後ろにeを定義しているので、employees表の別名はeとなります。

departments表も同じように定義しているので、departments表の別名はdとなります。
上記実行例の方が大分見やすくなりました。

このように表名の別名を定義することで表修飾が楽になります。
ただし、注意点は表別名を定義したら必ず別名を使用しないとエラーとなります。

  SQL> SELECT e.first_name,e.dept_id,d.dept_name
    2  FROM employees e
    3  JOIN departments d
    4  ON employees.dept_id = departments.dept_id;
  ON employees.dept_id = departments.dept_id
                         *
  ERROR at line 4:
  ORA-00904: "DEPARTMENTS"."DEPT_ID": invalid identifier


上記例はON句は表別名を使用しなかった為にエラーとなっています。

では、色々な実行例を見ていきましょう。

  SQL> SELECT e.first_name,e.dept_id,d.dept_name
    2  FROM employees e
    3  JOIN departments d
    4  ON e.dept_id = d.dept_id
    5  WHERE e.dept_id = 20;

  FIRST_NAME                        DEPT_ID DEPT_NAME
  ------------------------------ ---------- ------------------------------
  Sato                                   20 consulting
  Kato                                   20 consulting
  Sinagawa                               20 consulting


上記実行例は結合と条件式を組合わせた例となります。WHERE句で指定可能ですが、
ANDでも問題ありません。

  SQL> SELECT e.first_name,e.dept_id,d.dept_name
    2  FROM employees e
    3  JOIN departments d
    4  ON e.dept_id = d.dept_id
    5  AND e.dept_id = 20;

  FIRST_NAME                        DEPT_ID DEPT_NAME
  ------------------------------ ---------- ------------------------------
  Sato                                   20 consulting
  Kato                                   20 consulting
  Sinagawa                               20 consulting


グループ関数の組み合わせも問題ありません。

  SQL> SELECT d.dept_name,d.dept_id,avg(salary)
    2  FROM employees e
    3  JOIN departments d
    4  ON e.dept_id = d.dept_id
    5  GROUP BY d.dept_name,d.dept_id;

  DEPT_NAME                         DEPT_ID AVG(SALARY)
  ------------------------------ ---------- -----------
  Human Resources                        10     5500000
  Human Resources                        50     4750000
  consulting                             20     7000000
  Engineering                            40     5960000
  sales                                  30  5333333.33


では次に3つ以上の表からのデータ取得となります。

[構文] 3つの表の結合
 SELECT 列名, 列名…
 FROM 表名1
 JOIN 表名2
 ON 表名1.列名 = 表名2.列名;
 JOIN 表名3
 ON 表名3.列名 = 表名1または表名2.列名;

3つの表の結合はJOIN句とON句を追加するだけです。
ON句には表名3と結合する列を指定します。
表3と結合する列は表1か表2のどちらかの列を指定します。

4つ目の表を結合する場合はさらにJOIN句とON句を追加するだけです。

では実行例です。

  SQL> SELECT e.first_name,d.dept_name,l.loc_name
    2  FROM employees e
    3  JOIN departments d
    4  ON e.dept_id = d.dept_id
    5  JOIN locations l
    6  ON l.loc_id = d.loc_id;

  FIRST_NAME                DEPT_NAME                LOC_NAME
  ------------------------- ------------------------ -----------------------
  Sato                      consulting               Tokyo
  Yamamoto                  Human Resources          Tokyo
  Watanabe                  Human Resources          Tokyo
  Watanabe                  Human Resources          Tokyo
  Kataoka                   Engineering              Oosaka
  Harada                    Engineering              Oosaka
  Koda                      Engineering              Oosaka
  Saito                     Engineering              Oosaka
  Akagi                     sales                    Oosaka
  Sato                      sales                    Oosaka
  Tao                       sales                    Oosaka
  Kaneko                    sales                    Oosaka
  Totsuka                   sales                    Oosaka
  Kato                      consulting               Tokyo
  Sinagawa                  consulting               Tokyo
  Kuroda                    Engineering              Oosaka
  Nakamura                  sales                    Oosaka
  Kamata                    Human Resources          Tokyo

  18 rows selected.


上記例では、表名1がemployees表、表名2がdepartments表、
表名3がlocations表で各結合を行っています。
このように複数表を結合する場合は、JOIN,ON句を追加し結合を行うことができます。

いかがでしたでしょうか。結合という複数表からのデータ取得方法を学んできました。
次回は結合の応用について学んでいきます。