HOW TO LEFT JOIN IN A VARRAY FIELD

Question

I need help on find the correct way to write a query in oralce db. Now I have this query:

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

In this query schema.tale_name a is the table which has multiples sql types. One of this is a.LIQUIDITA_DERVT.MARGINI_DERVT which is a varray of another sql type nested in a.LIQUIDITA_DERVT which is also a sql type.

The problem with this query is that it only retrieves rows where a.LIQUIDITA_DERVT.MARGINI_DERVT exist but I need to retrieve all the rows since there isn't a where condition.

Answer 1

If you're on a recent version of Oracle (12c+) you can use outer apply:

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

In earlier versions (as you've tagged this 11g) you can probably get away with a normal outer join with a dummy join condition, though this might break down with a more complicated query:

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

fiddle