CROSS APPLY Explained

My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today.

I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper.

[cc lang=”sql”]
SELECT
SalesOrderID = soh.SalesOrderID
,OrderDate = soh.OrderDate
,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
[/cc]

It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner:

[cc lang=”sql”]
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN
(
SELECT
max_unit_price = MAX(sod.UnitPrice),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID
[/cc]

What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic inside the derived table got so complex, that it would not limit the result set of the derived table by inferring the correlation first. This made this kind of query sometimes impractical.

Luckily, this is where the CROSS APPLY steps in so nicely. It gives us the best of both worlds by allowing us to correlate AND not have the query embedded in the select list:

[cc lang=”sql”]
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod
[/cc]

The other advantage this has over the correlated sub query is when we want to add more columns in our SELECT list, we do not have to completely repeat the entire query. We still have it in one place, making it somewhat modular. So instead of this:

[cc lang=”sql”]
SELECT
SalesOrderID = soh.SalesOrderID
,OrderDate = soh.OrderDate
,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) — 1
,SumLineTotal = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) — 2
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
[/cc]

We have this:

[cc lang=”sql”]
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
,sod.sum_line_total
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
,sum_line_total = SUM(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod
[/cc]

As for the execution plans, in my experience CROSS APPLY has always won. Not always by a lot, but it still wins.

So what is OUTER APPLY? It’s equivalent to a left join on the derived table.

[cc lang=”sql”]
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
LEFT JOIN
(
SELECT
max_unit_price = MAX(sod.UnitPrice),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID
[/cc]

[cc lang=”sql”]
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
OUTER APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod
[/cc]

64 comments
Delilah 13 Oct 2017 at 1:41 am

Well explained, thanks very much.

Mike Cheel 22 Aug 2017 at 3:52 pm

FYI: When I view the page the sql formatting appears to have been lost.

MD Luqman 15 Aug 2017 at 6:48 pm

Will be required on

Kit Lemmonds 29 Jul 2017 at 3:01 pm

This is still one of the best CROSS APPLY articles out there some six+ years later. Great job!

The one part I’m unsure about is performance. The execution plans for all three statement versions here are 33% relative to batch (after removing the CROSS APPLY’s “sum_line_total” that was causing its cost to jump to 76% relative to batch). The reads at each step are identical in all plans. Perhaps optimizer improvements in MSSQL16 have improved the performance of the correlated subquery. If so, the argument for CROSS APPLY would seem to be readability.

Jason Freeman 21 Apr 2017 at 12:29 am

Just wanted to thank you for posting this. I know sometimes blogs feel more like messages in bottles thrown into the sea. But this one found me and it was extremely timely. I spent about 6 hours working on the LEFT JOIN approach and as you described it just didn’t seem right. I now believe, based on your definition of the OUTER APPLY that I understand it.

bigkampe 11 Jan 2017 at 8:19 pm

Whoa! That’s easy and you explained it well! Many times I write a common table expression. For example, WITH blaTable AS (SELECT MAX(whatever), fk_ID FROM someTable GROUP BY fk_ID)….

While this works well, I’ve often wanted a more logical looking way especially when the queries get long.

L Parsons 27 Nov 2016 at 10:52 pm

Awesome explanation. Thank you. I’ve used CROSS APPLY before but only in a single context. I struggle with Joins that have select statements and subqueries within select statements because it’s just not how my brain works… Cross Apply just makes sense to me.

Ryan Anderson 18 Nov 2016 at 11:19 pm

Seriously, this was so helpful! Totally allowed me to perform further calculations on calculated columns from my cross applied query in my main query. Thank you!

Armen Alexanian 14 Oct 2016 at 10:00 pm

These examples are fantastic and very helpful! Thanks very much for this awesome article.

Mayank 14 Oct 2016 at 4:15 am

A Good Note…
Thanks

Alistair 17 May 2016 at 5:26 pm

I don’t understand why you’re using CROSS APPLY here instead of simply the following standard join + group:

SELECT soh.SalesOrderID, soh.OrderDate,
MAX(sod.UnitPrice) AS max_unit_price,
SUM(sod.LineTotal) AS sum_line_total
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY soh.SalesOrderID, soh.OrderDate

Can you explain why CROSS JOIN makes a difference in your examples?

Steve 18 Aug 2016 at 11:55 pm

He’s using a CROSS APPLY, not a CROSS JOIN.
“Can you explain why CROSS JOIN makes a difference in your examples?” – Alistair

CROSS JOIN is completely different than a CROSS APPLY. A CROSS JOIN returns a combination of all records (a Cartesian product) found in both tables. For example, if # of rows in table A = 100 and # of rows in table B = 5, a CROSS JOIN between the 2 tables (A * B) would return 500 rows total.

A CROSS APPLY functions similarly to an INNER JOIN as far as rows returned (the data and # of rows would be the same), except that performance is usually better. A derived query like the author is using isn’t how I’d typically script it (instead, you might as well use a Common Table Expression and join on the CTE, since that’s typically better for performance).

However, a CROSS APPLY can be especially useful with a table-valued function. In your query, CROSS APPLY to a table-valued function (with what would’ve been the derived query, instead, encapsulated inside of the function), and no “ON” clause needed. SalesOrderID is passed to the SQL function as a parameter. Like this…

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
,sod.sum_line_total
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY tblValuedFunction_sod (soh.SalesOrderID) AS sod

Of course, typically, the above would be done when you want to reuse the logic that’s in the function in multiple SQL views/functions (otherwise, just use a CTE).

Jesse James Burton 13 May 2016 at 3:49 pm

Great explanation! Super easy to follow. Thank You!

MT 05 Feb 2016 at 5:00 pm

This is a very clear, concise and complete explanation – thank you!!

Gene 01 Feb 2016 at 7:02 pm

They are correct! You should be a published author! I understood it so well!!

Tim 21 Jan 2016 at 10:15 am

Why couldn’t you use

SELECT
soh.SalesOrderID
,soh.OrderDate
,MAX(sod.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY soh.SalesOrderID
,soh.OrderDate

in your first example of cross apply? It seems to me even more legible.

Stewart 10 Feb 2016 at 5:39 pm

One could indeed. But if you want to include aggregates from multiple tables, you could get into a mess trying to do it that way. On the other hand, CROSS APPLY is much more scalable.

Pat Greene 20 Jan 2016 at 5:21 pm

This is a very good article on the use of CROSS APPLY. I ran these queries against an Adventureworks database at our site and I did find one thing that surprised/confused me. I set STATISTICS IO ON and when comparing the two queries below, I would have thought they would have been at least equivalent in scans and logical reads, but the latter query had counts much higher. What am I missing?

SELECT
SalesOrderID = soh.SalesOrderID
,OrderDate = soh.OrderDate
,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) — 1
,SumLineTotal = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) — 2
FROM AdventureWorks.Sales.SalesOrderHeader AS soh

Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, . . .
Table ‘SalesOrderDetail’. Scan count 2, logical reads 2482, . . .

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
,sod.sum_line_total
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
,sum_line_total = SUM(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

Table ‘SalesOrderDetail’. Scan count 31465, logical reads 101815, . . .
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, . . .

Matt 09 May 2016 at 11:38 am

The stats obtained are the acid test about whether a technique is useful or not. In this case I think that the query optimiser is unable to work out what NEEDS to be done to satisfy the query as opposed to what you are telling it to do. It may be clearer to a human but the optimiser is obstructed. Perhaps the conclusion is that we should stick to using APPLY when table valued functions have to be used (i.e. the use for which MS recommends this compnent of SQL)

Pete 25 Nov 2015 at 7:16 pm

Appreciated!

Saher 05 Oct 2015 at 2:53 pm

Great explanation!

Henry Lee 30 Sep 2015 at 12:57 pm

I never reply to these kinds of things either, but this was so well written and clear that I now feel pretty comfortable with cross apply. I came across it maintaining someone else’s code that was using cross apply on two function calls, and it wasn’t quite clear how it was working. This has been very helpful. Write a book on SQL 🙂

DB 17 Sep 2015 at 1:13 pm

Best explanation via example I’ve seen on this! Now if only I could get you Sql Server people to use “select 1 as a” instead of “select a=1”. 🙂

Derek Dieter 28 Mar 2016 at 7:58 pm

No doubt.. that is a BAD habit I agree.. I’m ridding myself of it as well.

Karel 03 Oct 2016 at 1:11 pm

Has its use…

WITH order_counts AS (

),
customer_order_counts AS (

)
— UN-comment next line to alternate between update and select
— UPDATE COC SET /*
SELECT *, — */
order_count = COC.new_order_cnt — COC.new_order_cnt AS order_count
, … = ….
FROM customer_order_counts AS COC;

similarly

— UN-comment next line to alternate between delete and select
— DELETE /*
SELECT * FROM –*/
BRANCH_INSTPAYMENTS
WHERE [Client_RecordId] = @ACCOUNTNO
AND AgreementNo = @AGREEMENTNO
–AND StatusDate > dateadd(day, -7, getdate()

Jim 13 Oct 2017 at 10:15 pm

Bad habit? Why?
It’s a lot more legible than using AS, since all your column names can be neatly aligned on the left and you don’t have to scroll horizontally back & forth, back & forth to find the name at the end of each unequal-length line.

Viv 26 Aug 2015 at 9:54 am
Tracey Gray 04 Aug 2015 at 9:20 pm

You should know that I NEVER comment on these types of sites, but this explanation was so excellent and concise that I felt the need to express my gratitude. Thank you and very well done!

Jamaal Nicholson 22 Aug 2015 at 1:31 am

I agree completely. I was reading the example provided by Microsoft online and they use a function that has a cte in it and it is going from other tables to that and the explanation was driving me crazy, I read this and it made ALL the more sense to me. Thanks Derek!

Lakshmi 01 Sep 2015 at 9:38 am

Thanks. This is good.

Bherb 10 Jul 2015 at 2:54 pm

Ok, So here is what i have..

SELECT Portfolio FROM [DBNmae].[dbo].[TableNM] AS SYN
INNER JOIN(
SELECT PARENTACCOUNT
FROM [DBServer].[DBName].[dbo].[TableNM] AS ARC
WHERE CONVERT(CHAR(10),POSTDATE,101)=CONVERT(CHAR(10),GETDATE()-1,101)
AND USERNUMBER!=898
AND USERNUMBER!=907
AND USERNUMBER!=800
AND USERNUMBER!=902
AND USERNUMBER!=808
GROUP BY PARENTACCOUNT) ARC
ON SYN.Portfolio = ARC.PARENTACCOUNT

What I need to do now, is use the results of the above query to pull the USERNUMBER associated with the query on the inner join.

Bherb 10 Jul 2015 at 3:05 pm

Yeah, Nevermind…Seems i just needed to add Usernumber is each select…

SJones 14 Apr 2015 at 4:55 pm

Nice job explaining this. I will be using this technique a lot.

Suheb 04 Apr 2015 at 6:07 pm

nobody in this world could tell the real use of Cross Apply, but you have done it and that too with a very simple example. Thanks !!!

sumith 24 Feb 2015 at 9:46 am

Nice article! Thanks.
Also thanking those who explained the difference between CROSS & OUTER ‘Apply’s

Taylor 14 Oct 2014 at 1:41 pm

AS a new dev just starting to write more complex sql queries without the use of ctes or inner joins, this was super helpful. Thanks!

Doidal 01 Oct 2014 at 8:18 pm

CROSS APPLY Explained – Really?
It isn’t explained at all. It’s simply exampled.

Terry 12 May 2015 at 7:56 pm

Doidal, This helped me to understand Cross Apply so call it what you will. I will just say thanks

Rizwan 02 Jun 2015 at 8:19 pm

An example is worth more than a 1000 words of explanation. I understood it faster and better than reading long texts, and I am sure many others understood as well. So it IS explained.

Darren 08 Sep 2014 at 3:21 pm

Thanks for the explanation and great examples. I see this getting a lot of use.

Joe B 22 Jul 2014 at 9:16 pm

Your explanation is great! I don’t see much of a difference between a join and a cross apply though.

Jaemis G 08 Jul 2014 at 11:45 pm

I agree, it is the best and easiest explanation I found, with good usage examples. But I learned something today.

In the first example with sub query as a column, all records in SalesOrderHeader will be returned, whether there’s a match in SalesOrderDetail or not (everyone knows this).

Second example, inner join to derived table with group, only the records where SalesOrderID is matched between Header and Detail table will be returned (everyone knows).

The third example with the cross apply, it is the same as the inner join. It will return data only where there is a match in SalesOrderID, like the inner join. This is what I learned. Cross Apply will filter out data if there is no match. Therefore, the first example and third example will not necessarily return the same dataset.

bubba jones 04 Jul 2014 at 2:15 pm

thank you for your article. I use cross apply and outer apply all the time and now i have a url to give to others so I don’t have to explain it.

Hemant Chandurkar 15 May 2014 at 7:32 am

Your article helped me to refresh my knowledge quickly. Thanks!

Thomas Gabriel 09 May 2014 at 8:57 pm

The 2nd example, with GROUP BY in sod, does not yield the same result as example 3 (first stmt with CROSS APPLY.) The 2nd example will only yield SalesOrderHeader records that have details but the 3rd example will yield all headers, with or without details. Now maybe in this DB all headers have details, but that is not necessarily true for other databases in which a CROSS APPLY may be useful.

Mayuranp 11 Mar 2014 at 10:04 am

Excellent article. Very nicely explained.

However, I have a question.

I expect the following query to exclude the null values from results, but it returns null for units column when there arent any matching receipt_ids in sales_detail table.

select h.receipt_id, d.units
from sales_header h
cross apply
(
select sum(d.units) as units
from sales_details d
where d.receipt_id = h.receipt_id
) d

Tom Pester 16 Feb 2014 at 3:52 pm

Great explanation indeed. It allows to correlate in a derived table and thus you can reference multiple columns in the select list.

John 05 Feb 2014 at 2:53 pm

I’ve been working in databases for years and have never come across a Cross Apply or Outer Apply. I’ve built some wild stuff with recursive querying and everything. I saw a query this morning online with an Outer Apply and had to look it up. I’ll be refactoring some of my recent work using this logic. I love the idea of not having to use a group by in a derived table. Thanks for the explanation!

Manny 28 Oct 2014 at 5:50 pm

I’ve been building queries for years and I keep forgetting about this. It saved my butt a year back, but this morning after a couple of hours of messing around and refusing to create double subqueries (example above, with two subqueries in the select), your article reminded me of outer apply again. It really does have a nice use at times, and is also massively faster than the execution was with the two sub-queries

Tam 25 Dec 2013 at 1:35 pm

This is the only CROSS APPLY article that I can say I have really understood. Thank you so much for helping.

tony 25 Feb 2014 at 6:01 pm

second that

Venkata 31 Mar 2014 at 2:17 pm
Rob 22 May 2014 at 12:56 pm

Third that…well done on supplying an example that wasn’t based around XML.

Jack W 03 Apr 2014 at 3:17 pm

I agree as well – very nice writeup.

Ed Dorsey 08 Nov 2013 at 1:36 pm

Nicely explained. Thanks

G 09 Oct 2013 at 6:54 am

came across today and it’s the simplest and best explanation of cross apply I have come across.thanks

Ravi Chandran 15 Aug 2013 at 3:49 am

The explanation was pretty good. Just the right one I needed. Thank you!

Vasili 20 Jul 2014 at 8:19 am

Thanks for your reply . I made some progress, after using non-cached cecioltlon at least it does not say No Data Available butIn the Recommended Tables Based on Contention ( this time I used exactly your example with AdentureWorks2012 and EXEC test1 ) the tables now appear but when I click on Sales Order Details I get Error A data source instance has not been supplied for the data source Scan Details’ . Since this is the same CTP1 , same database, same procedure this looks strange . Any idea?

Jn_Developer 25 Jul 2013 at 9:27 pm

Excelente explicación, Muchas Gracias!!!

Activemind 22 Jul 2013 at 6:14 pm

Love the way you explain this. Definitely very helpful, thank you.

Armen 16 Apr 2013 at 3:30 pm

This explanation was really excellent and much better than many of the others I’ve seen on this topic. I never liked having to repeat the same correlated subquery multiple times in the select statement. Now I don’t have to! Thanks!

Bryan C 10 Apr 2014 at 1:13 pm

Great explanation and example! Thanks. I am wondering if the same effect might be accomplished using a common table expression? In other words, the part that would be the correlated subquery were defined as a CTE grouped as required and then joined into the main query. I only ask because I need to do something like this and the technical spec actually used the Crossapply approach so I am wondering if I am missing something or a CTE could also work.

Thanks,

Bryan

Neil Gundel 01 May 2015 at 7:38 pm

I was thinking the same thing.
More readable than most other approaches, and extremely flexible.
For example:

WITH MaxPrice as
(SELECT
SalesOrderID,
MAX(sod.UnitPrice) as max_unit_price
From Sales.SalesOrderDetail
GROUP BY
SalesOrderID
)

SELECT
soh.SalesOrderID
,soh.OrderDate
,mp.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
LEFT JOIN
MaxPrice mp
ON soh.SalesOrderID = mp.SalesOrderID

Derek Dieter 14 May 2015 at 4:14 pm

In this case it’s much more likely correlation will happen after the group by operation. With apply, the correlation is more likely to only select the necessary records to join on. That and a cte will almost always create a worktable prior to joining.

Featured Articles

 Site Author

  • Thanks for visiting!