In general, all subsearches have limitations, but most SQL people come to Splunk and think that join is the way to go, which is not the case. The first choice should always be to NOT use join. Append will also have limits on the number of results - there are plenty of discussions on the topic and Splunk has documentation on these limits. So, really, if your data size is large, you need to be aware of these limits, but also from a performance point of view, join is not the best way to go and searches using join will impact on other users on the search head.
... View more