Skip to Content

Left Outer Join vs Left Outer to One Join in CDS

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!

Add comment
10|10000 characters needed 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.

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 08, 2016 at 06:38 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2016 at 04:27 PM

    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

    Add comment
    10|10000 characters needed characters exceeded