Geeks With Blogs
Jim Giercyk

Many SQL developers shy away from the CROSS APPLY, because it is not very well understood.  There is not a lot of documentation or examples available online; CROSS APPLY is the SQL Query’s Redheaded step-child, but also a well-kept secret. 

Essentially, all a CROSS APPLY does is connect 2 queries that cannot be resolved as stand-along queries.  Using the AdventureWorks2008R2 database, I have put together a simple example:

 

USE AdventureWorks2008R2
 
  SELECT    p.FirstName + ' ' + p.LastName 'Sales Person',
            CAST(sp.SalesQuota AS MONEY) 'Sales Quota' ,
            CAST(ca.TotalSales AS MONEY) 'Total Sales' ,
            sp.CommissionPct 'Commission Percentage',
            ca.TotalCommission 'Total Commission',
            ca.TotalCommission / 12 'Gross Monthy Income',
            sp.Bonus 'Yearly Bonus',
            t.NAME 'Territory'
  FROM      Person.Person p
            JOIN Sales.SalesPerson sp ON p.BusinessEntityID = sp.businessentityid
            JOIN sales.SalesTerritory t ON t.TerritoryID = sp.TerritoryId
            CROSS APPLY ( SELECT    SUM(TotalDue) 'TotalSales' ,
                                    SUM(TotalDue) * sp.CommissionPct 'TotalCommission'
                          FROM      Sales.SalesOrderHeader s
                          WHERE     s.SalespersonID = sp.BusinessEntityId
                        ) ca
  WHERE     p.PersonType = 'SP'
            AND sp.SalesQuota IS NOT NULL
            ORDER BY sp.TerritoryId
 

So, what’s happening here?  One of the best uses for a CROSS APPLY is Aggregate Functions.  The top-level query requires the TotalSales and TotalCommision for each sales person.  The CROSS APPLY supplies these values by using the SUM() aggregate function.  Since the CROSS APPLY query is resolved first, we can think of it as “returning” the aggregated values to the top-level query.  Why is this important?  I’m glad you asked.  In this case, there are 3 major advantages to a CROSS APPLY over a simple JOIN to the SalesOrderHistory table.

1. Aggregate functions are only executed once no matter how many calculations the totals are used in the top-level query (notice that the ca.TotalCommission field is used twice)

2.  There is no need to use a nasty GROUP BY clause containing all of the top-level fields that are not part of an aggregate

3.  We can use ANY fields in the ORDER BY clause without including them in the top-level query.

Here is what the query would look like using a JOIN instead of a CROSS APPLY:

USE AdventureWorks2008R2

  SELECT    p.FirstName + ' ' + p.LastName 'Sales Person' ,
            CAST(sp.SalesQuota AS MONEY) 'Sales Quota' ,
            CAST(SUM(s.TotalDue) AS MONEY) 'Total Sales' ,
            sp.CommissionPct 'Commission Percentage' ,
            SUM(s.TotalDue) * sp.CommissionPct 'TotalCommission' ,
            ( SUM(s.TotalDue) * sp.CommissionPct ) / 12 'Gross Monthy Income' ,
            sp.Bonus 'Yearly Bonus' ,
            t.NAME 'Territory'
  FROM      Person.Person p
            JOIN Sales.SalesPerson sp ON p.BusinessEntityID = sp.businessentityid
            JOIN sales.SalesTerritory t ON t.TerritoryID = sp.TerritoryId
            JOIN Sales.SalesOrderHeader s ON s.SalespersonID = sp.BusinessEntityId
  WHERE     p.PersonType = 'SP'
            AND sp.SalesQuota IS NOT NULL
  GROUP BY  p.FirstName ,
            p.LastName ,
            sp.SalesQuota ,
            sp.CommissionPct ,
            sp.Bonus ,
            t.NAME
            --ORDER BY sp.TerritoryId

Doing the aggregates once may not make a lot of difference in this simple query, but in an environment that does heavy calculations such as engineering applications, there are often times when an aggregate is a common MAX() or MIN(), and is required to calculate many output columns in the top-level query.  There are also occasions where you need multiple aggregated fields to do a calculation…..in that case, readability is also improved in the top-level query.

The GROUP BY clause only contains 6 columns in this case, but what if you needed to return 50 columns or more.  With the columns listed twice, the number of lines in the query almost doubles.  Also, from a maintenance/enhancement standpoint, you double the amount of touch points when you are adding or changing a column.

Finally, the ORDER BY clause.  In the second example above, it is impossible to order by TerritoryID because it is not returned in the SELECT statement.  You would need to do another step if you were required to produce your results ordered by TerritoryId.  In the first query, you can order by any field or fields since there are no aggregate functions in the top-level query. 

I hope this makes the CROSS APPLY a little more understandable.  You can certainly go your entire career without coding one, but there are times when it can save you a lot of work, and in a high-volume environment, it may even help the efficiency of your queries.

Posted on Friday, January 3, 2014 10:30 AM | Back to top


Comments on this post: CROSS APPLY: SQLs Red-Headed stepchild

# re: CROSS APPLY: SQLs Red-Headed stepchild
Requesting Gravatar...
There's nothing wrong with your example, but you left out the other option of joining to a derived table, which would accomplish the same thing with almost the same syntax, only probably using a different query plan, which may be good or bad. CROSS/OUTER APPLY carry the same warnings as correlated subqueries, which they basically are, so they can perform badly if being correlated to a large number of rows, but they can perform very well vs the derived table if being correlated to a small number of rows.
Left by Bruce Dunwiddie on Jan 04, 2014 1:10 PM

# re: CROSS APPLY: SQLs Red-Headed stepchild
Requesting Gravatar...
Bruce,

You are absolutely right. My intent was to demonstrate the CROSS APPLY against a common type of query; A regular JOIN is what I see most often when I am looking at code, but as they say, your results may vary. Some alternatives to the CROSS APPLY can be seen in another entry on my blog, but again, the examples are certainly not all-inclusive.

http://geekswithblogs.net/NibblesAndBits/archive/2013/10/16/performance-considerations-for-common-sql-queries.aspx

Thanks for your feedback! It's always good to compare notes.

Jim
Left by Jim Giercyk on Jan 07, 2014 9:08 AM

# re: CROSS APPLY: SQLs Red-Headed stepchild
Requesting Gravatar...
nice post Mr Hami
Left by adsadfafds on Jan 25, 2018 1:01 AM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net