今まで学んできた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;
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 ( 列名 );
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.列名;
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.列名;
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句を追加し結合を行うことができます。
いかがでしたでしょうか。結合という複数表からのデータ取得方法を学んできました。
次回は結合の応用について学んでいきます。