Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type t_margini as object (GROUPa number);
create type tv_margini as varray(10) of t_margini;
create type t_liquidita as object (address varchar2(10), MARGINI_DERVT tv_margini);
create table tale_name (ID number, LIQUIDITA_DERVT t_liquidita);
insert into tale_name (ID, LIQUIDITA_DERVT)
values (1, t_liquidita('Rome',
  tv_margini(t_margini(11), t_margini(12), t_margini(13))));
1 rows affected

insert into tale_name (ID, LIQUIDITA_DERVT)
values (2, t_liquidita('Venice', null));
1 rows affected

insert into tale_name (ID, LIQUIDITA_DERVT)
values (3, t_liquidita('Pisa',
  tv_margini(t_margini(14), t_margini(15))));
1 rows affected

SELECT a.ID, a.LIQUIDITA_DERVT.ADDRESS, b.GROUPa
FROM tale_name a,table(a.LIQUIDITA_DERVT.MARGINI_DERVT) b


ID
LIQUIDITA_DERVT.ADDRESS
GROUPA


1
Rome
11

1
Rome
12

1
Rome
13

3
Pisa
14

3
Pisa
15


-- which is the same as...
SELECT a.ID, a.LIQUIDITA_DERVT.ADDRESS, b.GROUPa
FROM tale_name a
CROSS JOIN table(a.LIQUIDITA_DERVT.MARGINI_DERVT) b


ID
LIQUIDITA_DERVT.ADDRESS
GROUPA


1
Rome
11

1
Rome
12

1
Rome
13

3
Pisa
14

3
Pisa
15


-- or in this case...
SELECT a.ID, a.LIQUIDITA_DERVT.ADDRESS, b.GROUPa
FROM tale_name a
CROSS APPLY table(a.LIQUIDITA_DERVT.MARGINI_DERVT) b


ID
LIQUIDITA_DERVT.ADDRESS
GROUPA


1
Rome
11

1
Rome
12

1
Rome
13

3
Pisa
14

3
Pisa
15


SELECT a.ID, a.LIQUIDITA_DERVT.ADDRESS, b.GROUPa
FROM tale_name a
OUTER APPLY table(a.LIQUIDITA_DERVT.MARGINI_DERVT) b


ID
LIQUIDITA_DERVT.ADDRESS
GROUPA


1
Rome
11

1
Rome
12

1
Rome
13

2
Venice
null

3
Pisa
14

3
Pisa
15


SELECT a.ID, a.LIQUIDITA_DERVT.ADDRESS, b.GROUPa
FROM tale_name a
LEFT JOIN table(a.LIQUIDITA_DERVT.MARGINI_DERVT) b ON 1=1


ID
LIQUIDITA_DERVT.ADDRESS
GROUPA


1
Rome
11

1
Rome
12

1
Rome
13

2
Venice
null

3
Pisa
14

3
Pisa
15