referential join confusion

[rajarshi_muhuri]

Referential Join Offers an optimizable inner join where the right table is not checked if no field from the right table is requested. Thus would not recognize if no record is found in the right table --> Record from the left is not removed from the result set, behaviour similiar to outer join.

This left and right without context /cardinality is confusing me . Does the above mean "LEFT OUT JOIN" ?

I am doing a model , where Referential join seems to work , but the explainations seems counter confusing .

L Table                     R Table

100                         100

100

100

200                         200

                              200

300

How would this behave ?

My understanding it since R Table has row with 100 , all 3 rows with 100 in the left table would be brought in

one row from Left Table for row 200 would be brought in

how ever no rows from L Table for row 300 will be brought

[former_member182277 · Accepted solution]

Hello Rajashri,

Please have a look on below thread.

http://scn.sap.com/thread/3150613

It might be helpful for you.

Regards,

Neha

[Former Member]

Hi Rajarshi,

If in your e.g., table R had another column say COLB and your first column was A and if your select statement to join was

Select L.COLA, R.COLB from L join R on L.COLA = R.COLA

this would do an innner join as you have explicitly requested for a column from table R and as you said only results where a join exists (100 and 200) will be brought back.

However if your select statement was:

Select L.COLA from L join R on L.COLA = R.COLA

this will do a left outer join as you haven't requested any fields fo output from the right table and so your result set will have all rows from L - 100, 200 and 300.

Hope that helps.

Thanks,

Anooj

PS: Please note that select statement above is only for the purposes of explaining it (if you execute them in real world, it will always only do inner join) .. in real world you would be consuming analytical or attribute views connected via referential joins through reports or SQL select on the "column views" and the HANA engine would dynamically decide to use inner or left outer join based on the output columns you have selected.