sorting two tables (full join)

Question

i'm joining tables like:

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

But the output should be:

1. row without null fields
2. row with null fields in tableB
3. row with null fields in tableA

Like:

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
;