新人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;

コメントを残す

メールアドレスが公開されることはありません。

eleven − nine =