By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1( T1ID int, T1Name varchar2(20), col2 varchar2(1) );
insert all
into table1 values(1,'Name1','X')
into table1 values(2,'Name2','Y')
select * from dual;
2 rows affected
create table table2( T2T1ID int, T2SubName varchar2(20), T2Column2 varchar2(1) );
insert all
into table2 values(1,'Test1Sub1','P')
into table2 values(1,'Test1Sub2','P')
into table2 values(1,'Test1Sub3','P')
into table2 values(2,'Test2Sub1','Q')
into table2 values(2,'Test2Sub2','Q')
select * from dual;
5 rows affected
select t1.t1id, nvl2(t1.t1name,'Test'||t1.t1id,'') t1name, t1.col2,
t2.t2subname, t2.t2column2
from table1 t1
full outer join table2 t2
on ( t1.col2 = t2.t2column2 )
order by nvl(t1id,2)||nvl(t2t1id,2), nvl(t2.t2subname,'0');
| T1ID | T1NAME | COL2 | T2SUBNAME | T2COLUMN2 |
|---|---|---|---|---|
| 1 | Test1 | X | null | null |
| null | null | null | Test1Sub1 | P |
| null | null | null | Test1Sub2 | P |
| null | null | null | Test1Sub3 | P |
| 2 | Test2 | Y | null | null |
| null | null | null | Test2Sub1 | Q |
| null | null | null | Test2Sub2 | Q |