Search code examples
sqlsql-servercross-apply

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.

Solution

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