Skip to Content

Table Elimination with Full Outer Joins vs Left Outer Joins

Oct 30, 2017 at 11:00 AM


avatar image

We see a discrepency in how HANA optimizes its queryplan with the use of "Full Outer Joins" versus "Left Outer joins".

See the attached file for our scenario:


Our question: Is it intended that in Full Outer Join scenarios all tables are queried even if the query doesn't include columns from tables? This isn't the case with Left Outer Joins

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Oct 30, 2017 at 11:28 AM

What are the join cardinalities you setup in the model? These determine possible join elimination.

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

Hi Lars,

I have tried 1..1 and 1..N both have the same output.

With the Left Outer join the cardinality defined the query path as you said. But if you made an aggregation after the Left Outer join, HANA would do a table elimination if you would only select fields from 1 table. We don't see the same behavior with the Full Outer join.


The full outer join is basically

Table_A left outer join Table_B
Table_A right outer join Table_B

Even if join elimination is possible for the first outer join, the second join works inverse to that.

In order to find all non-matches of Table_B in Table_A (where Table_A would produce NULLs) Table_B has to be probed against Table_A.

So, there's no option to skip that and leave out that join. Also remember, that the join cardinality provides information on the upper boundary of joined records (how many matching records can be expected on the "other" end of the join), but not on how many non-matches to expect.

When you add aggregation it may be possible (depending on the aggregation) to push down the calculation and avoid the join altogether. But that has nothing to do with join elimination. It's rather a more general query rewriting step.