たとえばこんな感じ
SQL
1create table tbl_a(id int not null ,unique(id),val int);
2create table tbl_b(id int not null ,unique(id));
3create table tbl_c(id int not null ,unique(id));
4insert into tbl_a values(1,100),(2,200),(3,250);
5insert into tbl_b values(2),(3),(4);
6insert into tbl_c values(4),(5),(6);
考え方
- joinしてcount(*)>0ならjoinしたものを表示
- count(*)=0ならtbl_aだけ
- 上記2条件は完全に競合する内容なので、両方実行してUNIONする
結果
SQL
1select tbl_a.* from tbl_a inner join tbl_b on tbl_a.id=tbl_b.id
2group by id having count(*)>0
3union all
4select * from tbl_a where(
5select count(*) from tbl_a inner join tbl_b on tbl_a.id=tbl_b.id
6)=0;
7
8select tbl_a.* from tbl_a inner join tbl_c on tbl_a.id=tbl_c.id
9group by id having count(*)>0
10union all
11select tbl_a.* from tbl_a where(
12select count(*) from tbl_a inner join tbl_c on tbl_a.id=tbl_c.id
13)=0;
14