新人SEのためのSQL 外部結合
データベースを扱う様になって、割と引っかかりやすいのが『外部結合』ではないでしょうか? この記事では、『外部結合』について解説したいと思います。
目次
2つの表での外部結合
下図のような「tab01」、「tab02」という2つのテーブルがあります。
それぞれ『id』列がキーとなっていてJoinできます。格納されているデータの分布は、上図の右側の緑色の表になります。
001、002は両方のテーブルにデータがあり、003～006は片方のテーブルにだけデータが入っています。
内部結合
まずは、内部結合です。
select * from tab01
inner join tab02 on tab01.id = tab02.id
order by 1
;
↓実行結果
ID NAME ID NAME
----------------------------------
001 カルビ 001 かるび
002 ロース 002 ろーす
両方のテーブルにデータのある、001、002だけが表示されます。
外部結合
続いてtab01を基準とする外部結合( left join、left outer join )です。
select * from tab01
left join tab02 on tab01.id = tab02.id
order by 1
;
↓実行結果
ID NAME ID NAME
---------------------------------
001 カルビ 001 かるび
002 ロース 002 ろーす
003 ハラミ
004 タン
tab02にデータのない003、004も表示されていますが、tab01にデータのない005、006は表示されません。
外部結合で外部結合先のテーブルに条件を指定したい場合
これが、間違いやすいところですので、きちんと理解するとともに、書くときは注意してください。
tab02については、id=001のデータのみ取り出したい場合です。
まずは↓間違いの例です。
/* 間違いの例 */
select * from tab01
left join tab02 on tab01.id = tab02.id
where tab02.name = 'かるび'
order by 1
;
where tab02.name = 'かるび' と書いてしまうと、
Joinによって得られた結果に対して、whereが作用してしまい、
tab02.name = 'かるび' のデータだけが抽出されます。
ID NAME ID NAME
--------------------------------
001 カルビ 001 かるび
正しくは以下となります。
select * from tab01
left join tab02 on tab01.id = tab02.id and tab02.name = 'かるび'
order by 1
;
Joinの『on』の記述の中に書くことがポイントです。
↓実行結果
ID NAME ID NAME
--------------------------------
001 カルビ 001 かるび
002 ロース
003 ハラミ
004 タン
tab02からは、「001 かるび」のデータのみが表示されました。
3つの表での外部結合
さて、2つの表での外部結合は比較的理解しやすいのですが、数が増えて来るとこんがらがってきます。ここでは3つの表で考えてみます。
以下の様な3つの表があります。
内部結合
内部結合だと以下のような記載になります。
select * from tab01
inner join tab02 on tab01.id = tab02.id
inner join tab03 on tab01.id = tab03.id
order by 1
;
結果↓
ID NAME ID NAME ID NAME
---------------------------------------------------
001 カルビ 001 かるび 001 Karubi
3つのテーブルすべてにデータの存在する「001」のみ取り出されます。
外部結合(その1)
続いて外部結合ですが、まずは、親⇒子⇒孫のケースです。
select * from tab01
left join tab02 on tab01.id = tab02.id
left join tab03 on tab02.id = tab03.id
order by 1
;
tab01に対してtab02を、tab02に対してtab03をjoinしています。
ID NAME ID NAME ID NAME
----------------------------------------------------
001 カルビ 001 かるび 001 Karubi
002 ロース 002 ろーす
003 ハラミ
004 タン
この場合、tab03の「003-Harami」はtab01に003があっても表示されません。tab02を経由してtab03がjoinされていますが、tab02には「003」がないからです。
外部結合(その2)
次に、親⇒子1、子2のケースです。
select * from tab01
left join tab02 on tab01.id = tab02.id
left join tab03 on tab01.id = tab03.id
order by 1
;
ここでは、tab01に対して、tab02とtab03をjoinしています。[その1]のSQLとの違いは、上のSQL文の太字の箇所のみです。
ID NAME ID NAME ID NAME
--------------------------------------------------
001 カルビ 001 かるび 001 Karubi
002 ロース 002 ろーす
003 ハラミ 003 Harami
004 タン
この場合は、tab03の「003-harami」はtab01とのjoinとなるので表示されます。
外部結合で外部結合先のテーブルに条件を指定したい場合
複数のテーブルの外部結合で、抽出条件が入ってくると、大分ややこしくなってきます。テストデータの設定が適当だと、テストでも発見できませんから注意してください。
select * from tab01
left join tab02 on tab01.id = tab02.id and tab02.name <> 'かるび'
left join tab03 on tab02.id = tab03.id
order by 1
;
tab01とtab02のjoinの際に、tab02に対して条件を指定しています。
この場合は、tab02、tab03ともに「001」は表示されません。
ID NAME ID NAME ID NAME
---------------------------------------------------
001 カルビ
002 ロース 002 ろーす
003 ハラミ
004 タン
次に、tab03に対して条件を指定します。
select * from tab01
left join tab02 on tab01.id = tab02.id
left join tab03 on tab02.id = tab03.id and tab03.name <> 'Karubi'
order by 1
;
この場合は、tab03の「001 Karubi」は表示されません。
ID NAME ID NAME ID NAME
--------------------------------------------------
001 カルビ 001 かるび
002 ロース 002 ろーす
003 ハラミ
004 タン
いかがでしょうか?
- 外部結合は、きちんと理解していないと、複雑になって来たときに、間違える可能性があります。
- そして、理解した上でテーブル側のデータを用意しないと、テストでも発見されない事があります。
今回使用したその他のスクリプト
その他のスクリプトを添付しておきます。
drop table tab01;
drop table tab02;
drop table tab03;
create table tab01
(
id char(3) ,
name varchar2(20)
)
;
create table tab02 (
id char(3) ,
name varchar2(20)
)
;
create table tab03 (
id char(3) ,
name varchar2(20)
)
;
truncate table tab01;
truncate table tab02;
truncate table tab03;
insert into tab01 values('001','カルビ');
insert into tab01 values('002','ロース');
insert into tab01 values('003','ハラミ');
insert into tab01 values('004','タン');
insert into tab02 values('001','かるび');
insert into tab02 values('002','ろーす');
insert into tab02 values('005','みの');
insert into tab02 values('006','ほるもん');
insert into tab03 values('001','Karubi');
insert into tab03 values('003','Harami');
insert into tab03 values('005','Mino');
insert into tab03 values('007','Liver');
commit;
/* SQLPLUS関連 */
column name format a10;