when choose CROSS APPLY and when EXISTS?
I read, CROSS APPLY is just like JOIN.. and I think JOIN can be accomplished with EXISTS also (correlated sub query)
I am confused, what is the difference in using CROSS APPLY and EXISTS?
when should I go for CROSS APPLY against EXISTS?
Answer
CROSS APPLY isn't just like a JOIN. A JOIN finds matching (or not matching) rows between two sets of data. CROSS APPLY is a method to run a query against every row of the thing you're applying it against. This can act as a filtering mechanism, something like how a JOIN works, but it's applying something to each row, so it needs to be thought of that way.
EXISTS in a sub-query is a completely different mechanism of filtering. It's a method of quick identification because it immediately short-circuits it's search when it finds something. The place you'd want to use EXISTS in general is when you're likely to get a hit on the filter criteria, thereby making the searches as short as possible. But, EXISTS doesn't find all matches. It just finds the first match and then stops searching.
So while you can arrive at the same results from these three different methods, use them as defined and you'll usally be right. If you're literally JOINing two sets of data, use JOIN. If you want to run a process, frequently a filter, against every row in a data set, use CROSS APPLY. If you want a fast filter on a likely positive match, use EXISTS.