对两个表进行排序（全连接）

Question

我正在连接如下表：

select * from tableA a full join tableB b on a.id = b.id

但输出应该是：

1. 没有空字段的行
2. tableB 中带有空字段的行
3. tableA 中带有空字段的行

类似：

a.id    a.name  b.id    b.name
5   Peter   5   Jones
2   Steven  2   Pareker
6   Paul    null    null
4   Ivan    null    null
null    null    1   Smith
null    null    3   Parker

Answer 1

create table a(id number, name varchar2(10));
insert into a(id, name) values(5, 'Peter');
insert into a(id, name) values(2, 'Steven');
insert into a(id, name) values(6, 'Paul');
insert into a(id, name) values(4, 'Ivan');

create table b(id number, name varchar2(10));
insert into b(id, name) values(5, 'Jones');
insert into b(id, name) values(2, 'Pareker');
insert into b(id, name) values(1, 'Smith');
insert into b(id, name) values(3, 'Parker');

select * from a full join b on a.id = b.id
order by
  case
    when a.id is not null and b.id is not null then 0
    when a.id is not null and b.id is null then 1
    when a.id is null and b.id is not null then 2 
    else 3
  end
;