How to convert full outer join query to O-R query?

Question

I'm converting relational database into object-relational in Oracle.

I have a query that uses full outer join in the old one.

Is it possible to write the same query for O-R database without explicitly using full outer join?

For normal inner join it simple, I just use dot notation together with ref/deref.

I'm interested in this in general so let's say the relational query is:

select a.attr, b.attr from a full outer join b on (a.fk = b.pk);

I want to know if it's a good idea to do it this way:

select a.attr, b.attr from a_obj a full outer join b_obj b on (a.b_ref = ref(b));

Answer 1

Say I have the entities 'sale history' and 'sale forecast'. For a given product and period, I want to see the actual sale versus the forecast sale. However any given period may not have had a forecast or an actual sale, so I use an SQL like :

SELECT NVL(f.product_id, h.product_id), NVL(f.period, h.period), 
       f.forecast_sale, h.actual_sale
FROM forecast f 
FULL OUTER JOIN history h ON h.product_id = f.product_id and h.period = f.period

Basically I am joining two child tables together on a common key. Full outer joins are rare in relational databases as normalisation would generally merge the entities with common keys. Left and right outer joins are more common as the typical use case is to select the parent and its children while requiring a row even if a parent has no children.

So if you have a full outer join, the first thing to examine is whether the data structure is correct.

The data structure in an object model is fixed or 'pre-joined'. If the data structures within the model don't readily support the production of a certain result set, then you pretty much have to go without (or at least code up a lot of functionality to extract and join the data manually).

If you post some details about the relevant data structures the advice may be more precise.