When do you choose CROSS APPLY and when is EXISTS?

Question

I read CROSS APPLY is like JOIN .. and I think JOIN can be executed using EXISTS (correlated subquery)

I am confused, what is the difference in using CROSS APPLY and EXISTS?

When do I need to switch to CROSS APPLY vs EXISTS?

Answer 1

CROSS APPLY is not like JOIN. JOIN finds matching (or non-matching) rows between two datasets. CROSS APPLY is a method to run a query on each line of what you use it with. It may act as a filtering mechanism, something like the way JOIN works, but it applies something to each line, so you need to think about it.

EXISTS in a subquery is a completely different filtering mechanism. This is a quick identification method, because he immediately closes his search when he finds something. The place where you want to use EXISTS in general is when you are likely to get hit by the filter criteria, thereby making the search as short as possible. But EXISTS does not find all matches. He simply finds the first match and then stops the search.

So, while you can get the same results from these three different methods, use them as specific, and you will be right in the usual way. If you are literally connecting to two datasets, use JOIN. If you want to start a process, often a filter, for each row in a data set, use CROSS APPLY. If you need a quick filter with a likely positive match, use EXISTS.

Answer 2

Answer the grant directly for money But from the point of view of personal style, I use exclusively OUTER APPLY. Here's a common template, all three of them do the same thing, but using an external application, when checking, you can briefly comment on "WHERE ca2.HasAddress =" Yes "" and add ca2.HasAddress to the main element to make sure your filter deletes the records you need, while the other two filtering methods do not provide the same transparency at the row level.

SELECT c.CustomerID FROM SalesLT.Customer c OUTER APPLY (SELECT TOP 1 'Yes' HasAddress FROM SalesLT.CustomerAddress ca WHERE c.CustomerID = ca.CustomerID) ca2 WHERE ca2.HasAddress = 'Yes' SELECT c.CustomerID FROM SalesLT.Customer c CROSS APPLY (SELECT TOP 1 'Yes' HasAddress FROM SalesLT.CustomerAddress ca WHERE c.CustomerID = ca.CustomerID) ca2 SELECT c.CustomerID FROM SalesLT.Customer c WHERE EXISTS (SELECT 1 FROM SalesLT.CustomerAddress ca WHERE c.CustomerID = ca.CustomerID)