SAK 図書館
SAK 図書館
MySQL 編9 - 表結合(join)、単純結合、等価結合、外部結合、再帰結合
様々な連結テーブルが作成できるが、ビューの作成(create view) はサポート
されていないようである。
■単純結合
・現実的に単純結合を使う機会はないだろう。
次の例では、test2m の各レコードに testm の全レコードが結合される。
test2m のレコード数 x testm のレコード数の結果が問い合わされる。
create table testm (
key1 char(8),
data1 int8,
data2 int8,
data3 int8
) type=InnoDB;
insert into testm values ('a001', 1, 2, 3);
insert into testm values ('a011', 1, 2, 3);
insert into testm values ('b002', 10, 20, 30);
insert into testm values ('c003', 100, 200, 300);
create table test2m (
key1 char(8),
code1 char(8)
) type=InnoDB;
insert into test2m values ('abc01', 'a001');
insert into test2m values ('abc02', 'a011');
insert into test2m values ('abc03', 'z999');
select * from test2m, testm;
select * from test2m cross join testm;
+-------+-------+------+-------+-------+-------+
| key1 | code1 | key1 | data1 | data2 | data3 |
+-------+-------+------+-------+-------+-------+
| abc01 | a001 | a001 | 1 | 2 | 3 |
| abc02 | a011 | a001 | 1 | 2 | 3 |
| abc03 | z999 | a001 | 1 | 2 | 3 |
| abc01 | a001 | a011 | 1 | 2 | 3 |
| abc02 | a011 | a011 | 1 | 2 | 3 |
| abc03 | z999 | a011 | 1 | 2 | 3 |
| abc01 | a001 | b002 | 10 | 20 | 30 |
| abc02 | a011 | b002 | 10 | 20 | 30 |
| abc03 | z999 | b002 | 10 | 20 | 30 |
| abc01 | a001 | c003 | 100 | 200 | 300 |
| abc02 | a011 | c003 | 100 | 200 | 300 |
| abc03 | z999 | c003 | 100 | 200 | 300 |
+-------+-------+------+-------+-------+-------+
■等価結合
・等価結合は、特定のキーで表を結合します。
条件のどちらかのデータが存在しない場合、結果セットには含まれません。
現実的には外部結合が一番使いやすいと思います。
select
test2m.key1,
code1,
data1,
data2,
data3
from test2m, testm
where test2m.code1 = testm.key1
;
+-------+-------+-------+-------+-------+
| key1 | code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001 | 1 | 2 | 3 |
| abc02 | a011 | 1 | 2 | 3 |
+-------+-------+-------+-------+-------+
・inner join を使用して、等価結合することもできます。(インナージョイン)
select
test2m.key1,
code1,
data1,
data2,
data3
from test2m inner join testm on test2m.code1 = testm.key1
;
・3 つ以上の結合を inner join で指定するには、次のようにする。
select
test2m.key1,
...
from (test2m inner join testm on test2m.code1 = testm.key1)
inner join test3m on test2m.code1 = test3m.key1
;
■非等価結合
・非常に時間のかかる結合ですが、必要な場面があるかもしれない。
なにがしかの範囲条件で結合する場合に使います。
select
test2m.key1,
code1,
data1,
data2,
data3
from test2m, testm
where test2m.code1 like concat(substring(testm.key1, 1, 2), '%')
;
+-------+-------+-------+-------+-------+
| key1 | code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001 | 1 | 2 | 3 |
| abc02 | a011 | 1 | 2 | 3 |
| abc01 | a001 | 1 | 2 | 3 |
| abc02 | a011 | 1 | 2 | 3 |
+-------+-------+-------+-------+-------+
■外部結合(外結合、行結合、行連結、テーブル結合、外部接合)
・外部結合は、一番よく使用します。
一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セ
ットに含める指定ができます。
(左結合、右結合、複数テーブル問い合わせ、複数テーブル参照)
(複数問い合わせ、複合テーブル参照、レフトジョイン、ライトジョイン)
select
test2m.key1,
code1,
data1,
data2,
data3
from test2m left join testm on test2m.code1 = testm.key1
;
+-------+-------+-------+-------+-------+
| key1 | code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001 | 1 | 2 | 3 |
| abc02 | a011 | 1 | 2 | 3 |
| abc03 | z999 | NULL | NULL | NULL |
+-------+-------+-------+-------+-------+
・right join は、left join の逆で、次のようにする。
select
test2m.key1,
code1,
data1,
data2,
data3
from test2m right join testm on test2m.code1 = testm.key1
;
+-------+-------+-------+-------+-------+
| key1 | code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001 | 1 | 2 | 3 |
| abc02 | a011 | 1 | 2 | 3 |
| NULL | NULL | 10 | 20 | 30 |
| NULL | NULL | 100 | 200 | 300 |
+-------+-------+-------+-------+-------+
・三つ以上の表を結合することもできます。(3 テーブル結合)
select
test2m.key1,
...
from (test2m left join testm on test2m.code1 = testm.key1)
left join test3m on test2m.code1 = test3m.key1
;
・複数の表を結合すると修飾が難解になりがちなので、テーブル別名を使うと
良いです。
select
a.key1 as key1,
code1,
data1,
data2,
data3
from test2m a left join testm b on a.code1 = b.key1
;
** 外部結合を明示するために left join を left outer join、right join
を right outer join と書くこともできる。
(アウタージョイン、レフトアウタージョイン、ライトアウタージョイン)
select
a.key1 as key1,
code1,
data1,
data2,
data3
from test2m a left outer join testm b on a.code1 = b.key1
;
■再帰結合(内部結合)
・再帰結合は、同じテーブルをテーブル別名を使用して結合します。
次の例では、得意先m に親得意先CD があるとして、その得意先名を自分自
身と結合しています。(自己結合)
select
a.得意先CD 得意先CD,
a.得意先名 得意先,
a.親得意先CD 親得意先CD,
b.得意先名 得意先
from 得意先m a, 得意先m b
where a.得意先CD = b.親得意先CD
;
■MySQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2018 Y.SAK