By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table postal_address (id, name, postal_address) as
select 123, 'Alex', 'Decker Hill A' from dual
union all
select 343, 'Flynn', 'Apartment A' from dual;
2 rows affected
create table electronic_address (id, name, phone_type, phone_number) as
select 123, 'Alex', 'Mobile', 99776 from dual
union all
select 123, 'Alex', 'Mobile', 99876 from dual
union all
select 343, 'Flynn', 'Mobile', 98798 from dual;
3 rows affected
select id, name, 1 as address_type, phone_type, phone_number, null as postal_address
from electronic_address
union all
select id, name, 2, null, null, postal_address
from postal_address
order by id, address_type;
| ID | NAME | ADDRESS_TYPE | PHONE_TYPE | PHONE_NUMBER | POSTAL_ADDRESS |
|---|---|---|---|---|---|
| 123 | Alex | 1 | Mobile | 99776 | null |
| 123 | Alex | 1 | Mobile | 99876 | null |
| 123 | Alex | 2 | null | null | Decker Hill A |
| 343 | Flynn | 1 | Mobile | 98798 | null |
| 343 | Flynn | 2 | null | null | Apartment A |
select coalesce(e.id, p.id) as id,
coalesce(e.name, p.name) as name,
e.phone_type,
e.phone_number,
p.postal_address
from electronic_address e
full outer join postal_address p on p.id = e.id
order by id;
| ID | NAME | PHONE_TYPE | PHONE_NUMBER | POSTAL_ADDRESS |
|---|---|---|---|---|
| 123 | Alex | Mobile | 99776 | Decker Hill A |
| 123 | Alex | Mobile | 99876 | Decker Hill A |
| 343 | Flynn | Mobile | 98798 | Apartment A |