Sorting two tables (full join)
I am joining tables like:
select * from tableA a full join tableB b on a.id = b.id
But the output should be:
- string without null fields
- a row with zero fields in tableB
- a row with zero fields in tableA
how
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
+2
a source to share
1 answer
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
;
+4
a source to share