CROSS APPLY could be used as a replacement for correlated sub queries and derived tables.  That’s what we’ll discuss today.

I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper.

SELECT
SalesOrderID           = soh.SalesOrderID,
OrderDate              = soh.OrderDate,
MaxUnitPrice           = (SELECT MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail sod
WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM AdventureWorks.Sales.SalesOrderHeader AS soh

It always seemed to me that these operations should go below the FROM clause.  So to get around this, I would typically create a derived table.  Which didn’t completely feel right either, but it was still just a bit cleaner:

SELECT
    soh.SalesOrderID,
    soh.OrderDate,
    sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN (
    SELECT
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID ) sod
ON sod.SalesOrderID = soh.SalesOrderID

What made this ugly was the need to use the GROUP BY clause because we could not correlate.  Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic inside the derived table got so complex, that it would not limit the result set of the derived table by inferring the correlation first.  This made this kind of query sometimes impractical.

Luckily, this is where the CROSS APPLY steps in so nicely.  It gives us the best of both worlds by allowing us to correlate AND not have the query embedded in the select list:

SELECT
     soh.SalesOrderID,
     soh.OrderDate,
     sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY (
     SELECT
     max_unit_price = MAX(sod.UnitPrice)
     FROM Sales.SalesOrderDetail AS sod
     WHERE soh.SalesOrderID = sod.SalesOrderID ) sod

The other advantage this has over the correlated sub query is when we want to add more columns in our SELECT list, we do not have to completely repeat the entire query.  We still have it in one place, making it somewhat modular.  So instead of this:

SELECT
     SalesOrderID           = soh.SalesOrderID ,
     OrderDate              = soh.OrderDate ,
MaxUnitPrice           = (SELECT MAX(sod.UnitPrice)
                               FROM Sales.SalesOrderDetail sod
                               WHERE soh.SalesOrderID = sod.SalesOrderID) ,
SumLineTotal           = (SELECT SUM(LineTotal)
                               FROM Sales.SalesOrderDetail sod
WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM AdventureWorks.Sales.SalesOrderHeader AS soh

We have this:

SELECT
    soh.SalesOrderID,
soh.OrderDate,
sod.max_unit_price,
sod.sum_line_total
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY (
SELECT
max_unit_price = MAX(sod.UnitPrice),
sum_line_total = SUM(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID ) sod

As for the execution plans, in my experience CROSS APPLY has always won.  Not always by a lot, but it still wins.

So what is OUTER APPLY?  It’s equivalent to a left join on the derived table.

SELECT
soh.SalesOrderID,
soh.OrderDate,
sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
LEFT JOIN (
SELECT
max_unit_price = MAX(sod.UnitPrice),
      SalesOrderID
FROM Sales.SalesOrderDetail AS sod
   GROUP BY sod.SalesOrderID ) sod
ON sod.SalesOrderID = soh.SalesOrderID
SELECT
soh.SalesOrderID,
soh.OrderDate,
sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
OUTER APPLY (
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID ) sod

Source:http://sqlserverplanet.com/sql-2005/cross-apply-explained

Advertisements

Share your thoughts

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s