Brad Brad - 1 month ago 8
SQL Question

SQL Cross Apply - is it needed - not clear on why it is being used in this example

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.

SELECT
LeadInventory.LoanNumber,
'Client Age',
PEMWeightByClientAge.PEMWeight,
LeadInventory.ClientAge,
PEMWeightByClientAge.PEMWeight,
#LoanPEMModelHybrid.PEMModel
FROM LeadInventory
INNER JOIN #LoanPEMModelHybrid ON dbo.LeadInventory.LoanNumber = #LoanPEMModelHybrid.LoanNumber
CROSS APPLY
(
SELECT PEMWeight =
CASE
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
ELSE 0
END
) AS PEMWeightByClientAge
WHERE #LoanPEMModelHybrid.PEMModel = 'Application'
AND LeadInventory.ClientAge>0





SELECT DISTINCT
IDENTITY(INT, 1,1) AS ID,
LoanNumber,
Calculation.Prob AS TotalPEMScoreForLoan,
PEMModelCode
INTO #PEMScoreHybridFinal
FROM #PEMScoreHybrid
CROSS APPLY
(
SELECT Prob =
CASE PEMModelCode
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))
END
) Calculation


Thank You.

Answer

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 CROSS APPLY.

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".