Join is much more efficient. Is it possible to fillnull on a join so that I can collect the results for events for which there isn't an event to join?
sourcetype=1 | join host [ search sourcetype=2 | fields host,result ] | table host,result
you can also set the join type to left for example :
sourcetype=1 | join type=left host [ search sourcetype=2 | fields host,result ] | table host,result
then you will see every restults from sourcetype, and where there is no events from sourcetype2, the field will only be empty. If you want in place of empty, a 0, then you can add a fillnull...
sourcetype=1 | join type=left host [ search sourcetype=2 | fields host,result ] | fillnull value=0 | table host,result
If this is related to your transaction question (http://splunk-base.splunk.com/answers/59493/mostmore-efficient-way-of-counting-incomplete-transactio...) , you may be disappointed here. I think join will run into subsearch limits and not give you the results you desire when there are enough rows to be joined.