Skip to Content
4

Left Outer Join vs Left Outer to One Join in CDS

Nov 08, 2016 at 05:07 AM

873

avatar image

Hello,

I'm intrigued by this new syntax that I ran into in ABAP CDS Views - LEFT OUTER TO ONE JOIN. I tried to find some documentation for this but couldn't. Also when I run the query with LEFT OUTER JOIN, the results are the same.

Is there some specific reason we should we use this and possible scenarios to use one or the other?

Thanks!

10 |10000 characters needed characters left characters exceeded

For the sake of completeness: The addition TO ONE can influence the result on databases that support it. If the selected data do not match the expected cardinality, the result set can depend on the SELECT list. It is then undefined and can be truncated. E.g. a left outer to one join from scarr to spfli is semantically wrong. If you specify it nevertheless, a count(*) on HANA counts only the lines from scarr because the DB believes that you did it right.

1
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Raymond Giuseppi
Nov 08, 2016 at 06:38 AM
2

AFAIK it's only some kind of optimisation (like SQL hints) of LEFT JOIN when there is a one-to-one relationship between the two tables. (one row of the left side of the LEFT OUTER JOIN results in exactly one row in the result set.)

Regards,
Raymond

Show 2 Share
10 |10000 characters needed characters left characters exceeded

So it seems that I can assume it doesn't affect the final result set but optimizes the SQL parsing for the SQL Engine?

Regards,

Shakul.

0

That was also my conclusion.

Regards,
Raymond

1
Horst Keller
Nov 09, 2016 at 04:27 PM
2

The ABAP documentation will be updated accordingly. The guys told me too late. Yes, it is kind of a DB hint for HANA and other DBs and its DB specific meaning should be documented e.g. in the latest HANA SQL reference. But if it is used wrongly, it can influence the result set (see comment to the question).

Horst

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your comment Horst!

Regards,

Shakul.

0