トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
2-1-6 同一定義の他テーブルと比較
SQLパズル
テーブル1
ID 名前 戦闘 采配
-- -------- ---- ----
1 織田信長 70 94
2 柴田勝家 86 85
3 前田利家 69 72
5 木下秀吉 59 91
6 徳川家康 null null
7 null null null
テーブル2
ID 名前 戦闘 采配
-- -------- ---- ----
1 吉法師 70 94
2 柴田勝家 86 null
4 明智光秀 61 87
5 木下秀吉 59 91
6 null null null
7 null null null
テーブル1のID、名前、戦闘、采配と、
テーブル2のID、名前、戦闘、采配を比較し(nullとnullは等しい扱いとする)
ID(プライマリキー)が等しくて値が違うデータと、
テーブル1、テーブル2の、片方のみに存在するデータを取得する。
出力形式は、
テーブル1のID(テーブル2のみに存在すればnull),テーブル2のID(テーブル1のみに存在すればnull),
テーブル1の名前,テーブル2の名前,テーブル1の戦闘,テーブル2の戦闘,テーブル1の采配,テーブル2の采配
とする。
出力結果
AID BID A名前 B名前 A戦闘 B戦闘 A采配 B采配
---- ---- -------- -------- ----- ----- ----- -----
1 1 織田信長 吉法師 70 70 94 94
2 2 柴田勝家 柴田勝家 86 86 85 null
3 null 前田利家 null 69 null 72 null
null 4 null 明智光秀 null 61 null 87
6 6 徳川家康 null null null null null
データ作成スクリプト
create table テーブル1(
ID number(1),
名前 char(8),
戦闘 number(3),
采配 number(3),
primary key(ID));
create table テーブル2(
ID number(1),
名前 char(8),
戦闘 number(3),
采配 number(3),
primary key(ID));
insert into テーブル1 values(1,'織田信長',70,94);
insert into テーブル2 values(1,'吉法師' ,70,94);
insert into テーブル1 values(2,'柴田勝家',86,85);
insert into テーブル2 values(2,'柴田勝家',86,null);
insert into テーブル1 values(3,'前田利家',69,72);
insert into テーブル2 values(4,'明智光秀',61,87);
insert into テーブル1 values(5,'木下秀吉',59,91);
insert into テーブル2 values(5,'木下秀吉',59,91);
insert into テーブル1 values(6,'徳川家康',null,null);
insert into テーブル2 values(6,null,null,null);
insert into テーブル1 values(7,null,null,null);
insert into テーブル2 values(7,null,null,null);
commit;
SQL
col aID for 999
col bID for 999
col a戦闘 for 99999
col b戦闘 for 99999
col a采配 for 99999
col b采配 for 99999
--■■■minusを使う方法■■■
select a.ID as aID,b.ID as bID,
a.名前 as a名前,b.名前 as b名前,
a.戦闘 as a戦闘,b.戦闘 as b戦闘,
a.采配 as a采配,b.采配 as b采配
from テーブル1 a full outer join テーブル2 b on a.ID=b.ID
where exists((select c.ID,c.名前,c.戦闘,c.采配 from テーブル1 c where c.ID = coalesce(a.ID,b.ID)
minus select c.ID,c.名前,c.戦闘,c.采配 from テーブル2 c where c.ID = coalesce(a.ID,b.ID))
union all
(select c.ID,c.名前,c.戦闘,c.采配 from テーブル2 c where c.ID = coalesce(a.ID,b.ID)
minus select c.ID,c.名前,c.戦闘,c.采配 from テーブル1 c where c.ID = coalesce(a.ID,b.ID)))
order by coalesce(a.ID,b.ID);
--■■■minusを使わない方法■■■
select a.ID as aID,b.ID as bID,
a.名前 as a名前,b.名前 as b名前,
a.戦闘 as a戦闘,b.戦闘 as b戦闘,
a.采配 as a采配,b.采配 as b采配
from テーブル1 a full join テーブル2 b
on a.ID=b.ID
where Least(a.ID,b.ID) is null
or a.名前!=b.名前 or (nvl(a.名前,b.名前) is not null and Least(a.名前,b.名前) is null)
or a.戦闘!=b.戦闘 or (nvl(a.戦闘,b.戦闘) is not null and Least(a.戦闘,b.戦闘) is null)
or a.采配!=b.采配 or (nvl(a.采配,b.采配) is not null and Least(a.采配,b.采配) is null)
order by nvl(a.ID,b.ID);
解説
テーブル1とテーブル2を、IDを結合条件として完全外部結合し、
exists述語で、
テーブル1とテーブル2との差集合もしくは、テーブル2とテーブル1との差集合の中に、
IDが存在するかチェックしてます。
coalesce(a.ID,b.ID)は、nvl(a.ID,b.ID)で代用できます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
minusを使わない方法では、下記のロジックを使ってます。
Least(X,Y) is null ⇔ 少なくとも1つがnull
nvl(X,Y) is not null ⇔ 少なくとも1つが非null
上記の2つの論理積が真 ⇔ 片方のみnull
---------------------------------------------------------
3項に拡張すると
Least(X,Y,Z) is null ⇔ 少なくとも1つがnull
coalesce(X,Y,Z) is not null ⇔ 少なくとも1つが非null
上記の2つの論理積が真 ⇔ X,Y,Zの少なくとも1つがnullで
X,Y,Zの少なくとも1つが非null