topic Is join order important ? in Getting Data In

Is join order important ?

avdbsql — Thu, 16 Apr 2015 15:04:18 GMT

Could someone explain why I have this kind of difference?
index=data sourcetype=st1 num=10 --> gives 2 results
index=data sourcetype=st2 num=10 --> gives 10 results

When I tried a join between st1 (first) and st2, I've got 2 results :
index=data sourcetype=st1 num=10 | join type=inner num [ search index=data sourcetype=st2 ] --> gives 2 results

When I tried a join between st2 (first) and st1, I've got 10 results :
index=data sourcetype=st2 num=10 | join type=inner num [ search index=data sourcetype=st1 ] --> gives 10 results

I heard that join is similar to SQL join but doesn't look that it works the same way. Any ideas?

Re: Is join order important ?

richgalloway — Thu, 16 Apr 2015 15:24:42 GMT

Yes, join order is important. Join type is also important. In an inner join, events from the main search are included only if they match an event from the subsearch. That is why you see the results you do. An outer join returns all events from both searches.

Re: Is join order important ?

ngatchasandra — Thu, 16 Apr 2015 15:26:55 GMT

Hi,
Join order have effect,

when you write index=data sourcetype=st1 num=10 | join type=inner num [ search index=data sourcetype=st2 ] and obtain 2 results ,is because the results of index=data sourcetype=st1 num=10 who is 10 is join with results from search search index=data sourcetype=st2 on value field type=inner and field num. This returns 2 results because there is 2 correspondence between the two search on type=inner num fields. This is normal that you obtained 2 results.

Thus, in the second case, is the same thing, because the first search is return 10 results who will join with results of second search . This returns 10 results because there is 10 correspondence between the two search on type=inner num fields.

This means that Join do correspondence between results of two search in both case.