CROSS APPLY was introducted as part of TSQL in SQL Server 2005. Origionally it was created as a way to join on table value functions. They are the perfect tool for TVF joins however what I’ve missed out on until recently is how they are also a great tool for table joins in certain situations.
For example let’s say we have a set that defines the second set it wants to join on. Imagine we have a setting for each user of our system that allows them to choose the amount of ads they want to see, we store this setting in a table called AdSettings, we then have a second table called Ads that we want to query to get the amount of ads each user has choosen.
Obviously in this case our system could query the AdSettings table to get the AdCount then perform a seperate query to get the TOP(X) ads but that wouldnt demonstrate the power of CROSS APPLY, so to make this more useful lets say we want to cache all the Users and their specified ads in a table called UserAds. The final Schema looks like this…
You can use this script to create the database and seed data if you want to follow along…
We can then use CROSS APPLY to populate the UserAds table with the following query.
Given this data
Our CROSS APPLY query will produce this…
You can see Gavin has 5 ads as the AdSettings for the Gavin user has an AdCount of 5 where as Sally has an AdCount of 3 and therefore only has 3 records in UserAds.