when choose CROSS APPLY and when EXISTS?

Question

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?

Benghazi

Besides Grant's comments I'd mention "CROSS APPLY" and "OUTER APPLY" are exotic operations. You can go for a long time getting lots of important work done without ever using APPLY. Not to discourage learning about it and keeping it in mind, but you'll find opportunities to apply APPLY are not daily. If you can borrow a copy of TSQL Querying by Itzik Ben-Gan, or read some pages online, he has a chapter called "TOP and APPLY". The applications there I'd say are 1) typical and 2) not very exciting. – Anastomose

Yes, +10 for Itzik Ben-Gan's book. It's a must own. – Portaltoportal

@GrantFritchey: +10 ?!! usually people give +1.. :D ;) – Benghazi

When I came in this morning I remembered that a co-worker just last Friday had recommended this SO question/answers on this related, more general question. I've only skimmed it but ~180 cumulative upvotes and my coworker can't be wrong. – Anastomose

@dotNETbeginner It's like elections in Chicago, vote early & often. – Portaltoportal

Answer 1

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.

Portaltoportal

Answer 2

Grant's response is right on the money But from a personal style perspective I use OUTER APPLY exclusively. Here's a common pattern, all three of these do the same thing, but by using outer apply, when validating you can briefly comment out the "WHERE ca2.HasAddress = 'Yes'" and add ca2.HasAddress to the main select to verify that your filter is removing the records you want while the other two methods of filtering dont offer 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)

Schuck

This is the same thing as these two statements being equivalent. SELECT c.CustomerID FROM SalesLT.Customer c INNER JOIN SalesLT.CustomerAddress ca ON c.CustomerID = ca.CustomerID SELECT c.CustomerID FROM SalesLT.Customer c LEFT OUTER JOIN SalesLT.CustomerAddress ca ON c.CustomerID = ca.CustomerID WHERE ca.CustomerID IS NOT NULL – Cresset

While these two statements are equivalent, the second allows you to easily verify the data by simply adjusting the statement to this so i can see what records are being removed. SELECT c.CustomerID, ca.CustomerID FROM SalesLT.Customer c LEFT OUTER JOIN SalesLT.CustomerAddress ca ON c.CustomerID = ca.CustomerID --WHERE ca.CustomerID IS NOT NULL – Cresset