cancel
Showing results for 
Search instead for 
Did you mean: 

Universe Best Practice - Outer Joins vs Aggregate Functions

alfons_gonzalez
Active Participant
0 Kudos

Hi,

We have been reviewing the document entitled "Created Relational Universes: Best Practices" available at the SCN and in the page 7  (Chose the join type section) it mentions the following statement:

Tip: Be careful! An outer join can alter the results of aggregate functions.

I was ware of the problem of using outer joins regarding of where clauses and perfomance issues, but I don't understand which is the scenario described by this comment. Could some one help us to understand the potential issue of using outer joins when using aggregate functions?

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Alfons,

I read the section mentioned about "Joins:Chose the join type" given on page 7.

I think, the 'Tip' is given based on the context that outer joins return a "null" row if one of the joined tables has no corresponding row in the other table.

So, effectively the columns used for aggregation and the Count() of rows

is affected by these undesired set of "null" records and values.

Practically, as a regular ANSI SQL developer, I don't see much of a problem though.

Most of the times,one is pretty clear about the situations where outer joins need to be used, or avoided.

Thanks,

Prathamesh