Oracle 11g Release 2

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

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