cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Predicate Inside Outer-Join Predicate

Former Member
0 Kudos

Hello experts,

I am trying to garner some SQL performance tuning tips out of the document 'SAP HANA Database - Development Guide' and one tip is as the following:

"The column engine does not native support filter predicates inside outer-join predicates. Filter predicates over the right hand side of a left outer join and filter predicates over the left hand side of a right outer join are exceptions because shifting those predicates to the selections invoked by the join operation produces the same results"

I have doubts on the statement marked black. In my understanding the statement seems to claim that the following 2 queries produce the same results

SELECT ...

    FROM t1 LEFT OUTER JOIN t2 on t1.c1 = t2.c1 and t2.c2 = 'A'

SELECT ...

    FROM t1 LEFT OUTER JOIN t2 on t1.c1 = t2.c1

WHERE t2.c2 = 'A'

Well, the above 2 sql statements look similar and the difference is subtle. But actually they DO NOT produce the same results. The first sql does produce a larger result set.

Can anyone of you maybe shed some light on this tip from the document?

Thanks and kind regards,

James

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

I couldn't really find the statement you mentioned in the current version of the Developer Guide SPS 06.

However I can tell you that by definition of SQL those statements have a slightly different meaning and thereby produce different results depending on the data.

The t2.c2 = 'A' in the second statements' WHERE clause is interpreted in an 'INNER JOIN' way, thereby for all rows in the result set this statement has to evaluate to true, otherwise they will be filtered out including the left hand side table rows.

The LEFT JOIN predicate instead only makes sure, that left hand and right hand side rows are matched together which satisfy the predicate, but if there's not a single match for a single left hand side row, the left hand side row will be in the result set anyway. (with null values as default for the right hand side result columns). So in easy words it hard filters only the right hand side, but not the left.

Regards Christian