19章 結合(非等価結合、外部結合)
前回は結合について学びましたが、引き続き結合について学んでいきます。
本章で紹介する内容は非等価結合と外部結合についてです。
非等価結合
ではまずは非等価結合を見ていきましょう。
前回ご紹介してきたのは自然結合、USING句、ON句を使用した結合となります。
これらの結合は、同じ値の行同士で結合するため、等価結合と呼びます。
等価結合とは結合する列が=(等価)の値同士で結合を行っている結合のことを言います。
ON句では表名.列名 = 表名.列名というように結合する列をイコールで関連付けています。
一方、非等価結合とは演算子が=以外で結合することを言います。
例えば、以下の表を使用して各年収ごとのランクを表示したいとします。
SQL> select * from grades;
GRADE HIGH_SAL LOW_SAL
---------- ---------- ----------
Rank A 10000000 7000000
Rank B 6999999 5000000
Rank C 4999999 3000000
上記は給与ごとの階級を確認できるテーブルです。
年収が700万から1000万の間の従業員はランクA
年収が500万から699万より小さい従業員はランクB
年収が300万から499万より小さい従業員はランクC
というように給与ごとに階級を確認できます。
では、非等価結合の実行例を確認していきましょう。
SQL> SELECT e.first_name,e.salary,g.grade
2 FROM employees e
3 JOIN grades g
4 ON e.salary BETWEEN g.low_sal AND g.high_sal;
FIRST_NAME SALARY GRADE
------------------------------ ---------- ----------
Kuroda 4000000 Rank C
Kamata 4000000 Rank C
Nakamura 4000000 Rank C
Yamamoto 5000000 Rank B
Sato 5400000 Rank B
Kaneko 5400000 Rank B
Totsuka 5400000 Rank B
Tao 5400000 Rank B
Watanabe 5500000 Rank B
Watanabe 6000000 Rank B
Harada 6200000 Rank B
Koda 6400000 Rank B
Akagi 6400000 Rank B
Kato 6500000 Rank B
Sinagawa 6500000 Rank B
Kataoka 6500000 Rank B
Saito 6700000 Rank B
Sato 8000000 Rank A
Suzuki 10000000 Rank A
19 rows selected.
上記の例では、結合条件でBETWEEN演算子を使用しています。
結合する列はsalary列の値はgrades表のlow_sal列とhigh_sal列の範囲であるという条件です。
grades表は3行あり、salary列の値が7000000〜10000000の範囲の従業員は
RANK Aの行と結合され、salary列の値が5000000〜6999999の範囲はRANK B、
salary列の値が3000000〜4999999の範囲はRANK Cの行と結合されます。
このようにイコール演算子ではなく、別の比較演算子を使用し、
その条件に合った一致した行と結合を行うことができます。
例ではBETWEEN演算子を使用していますが、そのほかの演算子も使用することができます。
以上が等価結合のご紹介となります。
外部結合
では次に外部結合についてご紹介していきます。
今まで学んできた結合は条件に一致した行を結合する内部結合と呼ばれる結合方法です。
では結合する列の値がNULLである行や結合できる値がない場合は結合ができません。
今までの結合でも実は結合できない行がありました。
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.
上記実行例は前回にも紹介した実行例です。
実行結果で出力された件数は18行であることが分かります。
employees表は20行のため、結合できなかった行が2行存在します。
SQL> SELECT first_name,dept_id FROM employees;
FIRST_NAME DEPT_ID
------------------------------ ----------
Suzuki
Sato 20
Yamamoto 10
Watanabe 10
Watanabe 50
Kataoka 40
Harada 40
Koda 40
Saito 40
Akagi 30
Sato 30
Tao 30
Kaneko 30
Totsuka 30
Kato 20
Sinagawa 20
Kuroda 40
Nakamura 30
Kamata 50
Ito
20 rows selected.
SQL> SELECT dept_id,dept_name FROM departments;
DEPT_ID DEPT_NAME
---------- ------------------------------
10 Human Resources
20 consulting
30 sales
40 Engineering
50 Human Resources
60 International
6 rows selected.
結合できなかった行はSuzuki,Itoさんです。
この2人のdept_idはNULLとなっているため、
結合ができませんでした。そのためemployees表は20行あるにも関わらず、
18行だけ出力されました。
このように結合列の値がNULLもしくはイコールとならなかった行は結合ができません。
ただ結合できなくても、表示はさせたいという場合もあります。
このような場合は外部結合を使用することで、結合できなかった行も含めて
表示することができます。
では外部結合の構文を確認していきましょう。
[構文] 外部結合
SELECT 列名 , 列名 …
FROM 表名1
[ LEFT | RIGHT | FULL ] OUTER JOIN 表名2
ON 表名1.列名 = 表名2.列名;
外部結合を行う場合、FROM句に表名1,LEFT OUTER JOINに表名2を指定します。
LEFTを指定した場合、OUTER JOINの左側にある表1の結合できなかった行を含め表示します。
RIGHTを指定した場合、OUTER JOINの右側にある表2の結合できなかった行を含め表示します。
FULLを指定した場合、両方の表1,2の結合できなかった行を含め表示します。
では実行例です。
SQL> SELECT e.first_name,d.dept_name
2 FROM employees e LEFT OUTER JOIN departments d
3 ON e.dept_id = d.dept_id;
FIRST_NAME DEPT_NAME
------------------------------ ------------------------------
Watanabe Human Resources
Yamamoto Human Resources
Sinagawa consulting
Kato consulting
Sato consulting
Nakamura sales
Totsuka sales
Kaneko sales
Tao sales
Sato sales
Akagi sales
Kuroda Engineering
Saito Engineering
Koda Engineering
Harada Engineering
Kataoka Engineering
Kamata Human Resources
Watanabe Human Resources
Ito
Suzuki
20 rows selected.
LEFT OUTER JOIN構文の左にある表はemployees表なので、
この表から取得する行はすべて表示します。
SELECT句で指定されているfirst_name列はeという表修飾があるので、
employees表から取得します。
Ito,Suzukiさんはdept_idがNULLのため結合できせんが、
外部結合により表示することができます。
そしてIto,Suzukiさんの行はdepartments表と関連付けができなかったので、
departments表から取得するdept_nameの列はNULLとなります。
では次の実行例です。
SQL> SELECT e.first_name,d.dept_name
2 FROM employees e RIGHT OUTER JOIN departments d
3 ON e.dept_id = d.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
International
19 rows selected.
今回の例ではRIGHT OUTER JOINを使用しているので、departments表なので、
この表から取得する行はすべて表示します。
SELECT句はdept_name列にdという表修飾があるので、departments表から取得します。
dept_name列の値にはInternationalという部門が存在します。この行はdept_idが60であり、
employees表にはdept_idが60の従業員が存在しないため、結合できませんでした。
しかし、外部結合によりdept_idが60の行が表示できました。
最後はFULL OUTER JOINです。これは両方の表から結合できなかった表を含めて表示します。
SQL> SELECT e.first_name,d.dept_name
2 FROM employees e FULL OUTER JOIN departments d
3 ON e.dept_id = d.dept_id;
FIRST_NAME DEPT_NAME
------------------------------ ------------------------------
Suzuki
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
Ito
International
21 rows selected.
FULL OUTER JOINの場合はemployees,departments表で
結合できなかった表もすべて表示します。
こちらが外部結合となります。
いかがでしたでしょうか。結合を行うことで複数表からもデータを
取り出すことができるようになりました。