准备数据:
create table class_a2(
id int not null,
name varchar(100) not null
);
insert into class_a2(id, name)
values(1, '田中'),
(2, '铃木'),
(3, '尹集院');
/*
id | name
----+--------
1 | 田中
2 | 铃木
3 | 尹集院
(3 rows)
*/
create table class_b2(
id int not null,
name varchar(100) not null
);
insert into class_b2(id, name)
values(1, '田中'),
(2, '铃木'),
(4, '西园寺');
/*
id | name
----+--------
1 | 田中
2 | 铃木
4 | 西园寺
(3 rows)
*/
思路:
-- 我的答案
select case when a_id is null then b_id else a_id end as id,
case when a_name is null then b_name else a_name end as name
from (select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from class_a2 a
full outer join class_b2 b
on a.id = b.id
where a.name is null
or b.name is null) tmp
order by id;
/*
id | name
----+--------
3 | 尹集院
4 | 西园寺
(2 rows)
*/
-- 书中的答案
-- 😱别我的方法少生成了一个视图
-- 😱没想到coalesce还有这种用法
select coalesce(a.id, b.id) as id,
coalesce(a.name, b.name) as name
from class_a2 a
full outer join class_b2 b
on a.id = b.id
where a.id is null
or b.id is null;
/*
id | name
----+--------
3 | 尹集院
4 | 西园寺
(2 rows)
*/
