I am working on a stored procedure I inherited and it has some Cross apply queries (see queries below). I have an experienced SQL developer but not so much for cross apply so there may be simple explanation I am just missing here.
My question is for these 2 queries it looks like the cross apply is just doing a function call and/or logic to create a column, and I could just do that in the select and remove the cross apply completely. If you could give some sort of explanation as to why I would appreciate it as well.
When I have seen Cross Apply used there has always been something IN the cross apply that would essentially join to the tables outside the cross apply. Something like this for example would be inside the cross apply to "join" the FROM tables to the tables in the cross apply: soh.SalesOrderID (table outside corss) = sod.SalesOrderID (table inside cross)
I am not seeing anything like that in the below 2 queries or a reason for it.
INNER JOIN #LoanPEMModelHybrid ON dbo.LeadInventory.LoanNumber = #LoanPEMModelHybrid.LoanNumber
SELECT PEMWeight =
WHEN LeadInventory.ClientAge<=70 AND LeadInventory.ClientAge>=62 Then @PEMWeightAge62To70
WHEN LeadInventory.ClientAge<80 AND LeadInventory.ClientAge>70 THEN @PEMWeightAge71To80
WHEN LeadInventory.ClientAge>=80 THEN @PEMWeightAge80Plus
) AS PEMWeightByClientAge
WHERE #LoanPEMModelHybrid.PEMModel = 'Application'
IDENTITY(INT, 1,1) AS ID,
Calculation.Prob AS TotalPEMScoreForLoan,
SELECT Prob =
WHEN 'Initial QQ' THEN CAST (EXP(@INITIALQQBASE+ TotalPEMScoreForLoan)/(EXP(@INITIALQQBASE+TotalPEMScoreForLoan)+1)*100 AS Decimal(9,2))
WHEN 'APPLICATION' THEN CAST (EXP(@APPLICATIONBASE+ TotalPEMScoreForLoan)/(EXP(@APPLICATIONBASE+TotalPEMScoreForLoan)+1)*100 AS Decimal(9,2))
In the first query, you could use a subquery. However, note that the variable
PEMWeightByClientAge.PEMWeight in the
CROSS APPLY is being used twice in the
SELECT. This gives a hint as to why the write chose
Why do you use
CROSS APPLY? There are several reasons. The most basic is to call a set-returning function on arguments from a table. There really isn't any other way to do that.
With a subquery,
APPLY is more general than joins. It can also optimize better. If you are uncomfortable with the SQL-Server-specific
APPLY, just realize that these are lateral joins and quite consistent with other database operations.
You have a third use-case. This gets around the fact that column aliases cannot be re-used in a
SELECT. Subqueries and CTEs are alternatives. Subqueries have the downside that (if properly indented), the nesting can leave lots of white space on the left. CTEs are a viable alternative. Which someone uses is really a matter of taste, rather than being "right" or "wrong".