Introduction
Hiya! This article will explain OUTER and CROSS APPLY and show you how to use them by means of sample code. OUTER and CROSS APPLY are unique to SQL Server so this article is intended for anybody using SQL in a SQL Server environment. It will also cover many examples of where you can use OUTER and CROSS APPLY and their pro's and con's.
Use cases in this article include:
- TOP
- UNPIVOT
- Multi-field expressions
- Using expressions in other expressions
- APPLY and TVFs
Explaining by example
Instead of giving definitions I would like to explain by example. Think of CROSS APPLY as a row-by-row INNER JOIN. If we have:
SELECT *
FROM Vehicles V
INNER JOIN MileageLog ML ON V.ID = M.VehicleID
to join a vehicle and its mileage log we could do exactly the same thing using CROSS APPLY:
SELECT *
FROM Vehicles V
CROSS APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML
These two queries will produce identical results. We could use OUTER APPLY instead of CROSS APPLY to get the same effect as a LEFT JOIN. That is
SELECT *
FROM Vehicles V
LEFT JOIN MileageLog ML ON V.ID = ML.VehicleID
will give the same results as:
SELECT *
FROM Vehicles V
OUTER APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML
Notice how our ON condition becomes a WHERE condition in the subquery. Also notice how we give an alias for the APPLY just like we can alias tables in a JOIN statement - this is required for APPLY statements.
Use case 1: TOP N Rows
These queries now do the same thing and the JOIN is easier to write and remember, so why on earth would we use APPLY instead?
Let's say that instead of all mileage log entries for every vehicle we now only want the last 5 entries for every vehicle. One way of doing this is with ROW_NUMBER, PARTITION BY and a nested query:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ML.VehicleID ORDER BY ML.EntryDate DESC) RN
FROM Vehicles V
INNER JOIN MileageLog ML ON V.ID = ML.VehicleID
) IQ
WHERE IQ.RN <= 5
Which would only return the first 5 entries for every vehicle. To do so using a CROSS APPLY statement:
SELECT *
FROM Vehicles V
CROSS APPLY (
SELECT TOP 5 *
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
ORDER BY ML.EntryDate DESC) ML
The are a few important things to take note of here:
- We can use
TOP inside a CROSS APPLY statement: Since CROSS APPLY works row-by-row it will select the TOP 5 items for every row of the Vehicles table. - We don't have to specify partitioning since
CROSS APPLY is always row-by-row. Think of it as a built in PARTITION BY clause that is always there. - The
ROW_NUMBER approach will add a new field where CROSS APPLY does not.
This allows us to do things that would normally be somewhat complex in much more expressible ways. If we want the
TOP 10 PERCENT rows without an
APPLY statement it would have to be something like:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ML.VehicleID ORDER BY ML.EntryDate DESC) RN
FROM Vehicles V
INNER JOIN MileageLog ML ON V.ID = ML.VehicleID
) IQ
INNER JOIN
(
SELECT ML.VehicleID, COUNT(*) AS RowCount
FROM MileageLog ML
GROUP BY ML.VehicleID
) MLCount ON IQ.VehicleID = MLCount.VehicleID
WHERE RN / cast(MLCount.RowCount as float) <= 0.1
As you can see this becomes a more complex query since we now require aggregates and single-row expressions in order to calculate our own percentages. It also very quickly becomes unclear what we were trying to do.
If we use CROSS APPLY doing this is simply:
SELECT *
FROM Vehicles V
CROSS APPLY (
SELECT TOP 10 PERCENT *
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
ORDER BY ML.EntryDate DESC) ML
Are you starting to see how CROSS APPLY can make your life easier?
Use case 2: UNPIVOT
UNPIVOT unfolds a single row into multiple rows. The syntax for UNPIVOT works well if you're doing single table UNPIVOTs and gets rather complicated when you're joining or doing multiple. I'm not going to cover UNPIVOT examples here for the sake of brevity - feel free to Google (or the search engine of your preference) a few examples before reading on.
If we have the following data (first row is column names) in the table tbl:
A B C D
E 1 2 3
F 4 5 6
We can unpivot it using a CROSS APPLY as follows:
SELECT A, Category, Value
FROM tbl
CROSS APPLY (
SELECT 'B' AS Category, B AS Value UNION ALL
SELECT 'C', C UNION ALL
SELECT 'D', D
) CA
Viola, that's it. It will unfold the data like such:
A Category Value
E B 1
E C 2
E D 3
F B 4
F C 5
F D 6
Which is the same results that UNPIVOT would give.
A few important things to note:
- We can use
UNION ALL inside a CROSS APPLY statement to work in the same what that UNPIVOT would. - Performance Note:
UNPIVOT has major performance impact in various situations as many readers may be aware due to joins on its data being required later in many circumstances. I have found situations with large unfold operations where APPLY is actually orders of magnitude faster than UNPIVOT. This is especially true where unfolding multiple fields in a single table (hence where the apply query has no join predicates) since the row-by-row nature is often faster than joins to bring together multiple fields. Specifically useful for systems storing multiple aggregates in single rows 
- When we combine
UNION ALL with TOP, WHERE, GROUP BY, etc we can now do interesting things whilst unfolding (like unfolding only the top 3 values that are not NULL, getting only ). - Remember that this is now an unfold operation which is already partitioned on a row-by-row basis - anything you unfold is combined with whatever data you already have in each row. This can be very useful in many situations.
Use case 3: Multi-field expressions
Lets say we want to know which day every vehicle travelled the furthest:
SELECT *, (
SELECT TOP 1 EventDate
FROM MileageLog ML
WHERE V.ID = ML.VehicleID ORDER BY DistanceTravelled DESC) AS DayMostTravelled
FROM Vehicles V
Simple enough, right? Doing this with OUTER APPLY looks like such:
SELECT *
FROM Vehicles V
OUTER APPLY (
SELECT TOP 1 EventDate AS DayMostTravelled
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
ORDER BY DistanceTravelled DESC
) CA
Only a few small changes in the code is necessary:
- Our expression moves into a
APPLY subquery outside the statement - Our alias is now inside the subquery
- Our
APPLY receives an alias that we do not have to directly use - Notice that we use
OUTER APPLY and not CROSS APPLY in this scenario. Using CROSS APPLY would have only shown rows that have MileageLog entries where OUTER APPLY will show those of all vehicles.
So if we now want to know the date and the distance travelled on that day?
SELECT V.*, IQ.EventDate AS DayMostTravelled, IQ.DistanceTravelled
FROM Vehicles V
OUTER JOIN (
SELECT VehicleID, EventDate, DistanceTravelled,
ROW_NUMBER() OVER (PARTITION BY VehicleID ORDER BY DistanceTravelled DESC) RN
FROM MileageLog
) IQ ON IQ.VehicleID = V.ID AND IQ.RN = 1
Since this is no longer a single field we now have to use JOIN and ROW_NUMBER to get our desired information. Doing this with OUTER APPLY on the other hand:
SELECT * FROM Vehicles V
OUTER APPLY (
SELECT TOP 1 EventDate AS DayMostTravelled, DistanceTravelled
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
ORDER BY DistanceTravelled DESC
) CA
This gives us an easy way to select multiple fields from a related row based on some condition.
Use case 4: Using expressions in other expressions
We can use CROSS APPLY to give expressions names and use them in other expressions.
SELECT V.*, CA1.AvgDistance, CA1.TotalDistance
FROM Vehicles V
OUTER APPLY (
SELECT Avg(DistanceTravelled) AS AvgDistance, Sum(DistanceTravelled) AS TotalDistance
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
) CA1
The query above simply gets the average and total distance travelled for each vehicle.
SELECT V.*, CA1.AvgDistance, CA1.TotalDistance, CA2.ServicesLeft
FROM Vehicles V
OUTER APPLY (
SELECT Avg(DistanceTravelled) AS AvgDistance, Sum(DistanceTravelled) AS TotalDistance
FROM MileageLog ML
WHERE V.ID = ML.VehicleID
) CA1
OUTER APPLY (
SELECT COUNT(*) AS ServicesLeft
FROM VehicleServicePlans VSP
WHERE VSP.VehicleID = V.ID
AND VSP.ServicePlanDistance > CA1.TotalDistance
) CA2
As you see we can add a second OUTER APPLY to now use the results of the first and do some additional calculations. Chaining APPLYs in this way makes it easy to seperate same-row logic into multiple sections.
Use case 5: APPLY and TVFs
APPLY also works with TVFs.
Let's say we have a TVF to get the fields of a table:
CREATE FUNCTION FieldsForTable (@tablename nvarchar(1000))
RETURNS TABLE
AS
RETURN
select * from sys.columns where object_id = object_id(@tablename)
If we now want to get the fields for all tables starting with an A we can do it using CROSS APPLY:
SELECT * FROM sys.tables T CROSS APPLY dbo.FieldsForTable(T.name)
WHERE T.name LIKE 'a%'
Note: This could obviously be done using a single JOIN statement - the example is exactly that and just demonstrates how to use CROSS APPLY with TVFs.
Notes on the performance of APPLY
Since APPLY works on a row-by-row level:
- It is usually slower than
JOIN due to its row-by-row nature. In many situations SQL Server's query planner will optimize APPLYs to run as if they are JOINs. - They will normally match the speed of using single-field expressions in a query since they act in the same manner and will be optimised similarly.
- For "multi-field expressions" they will mostly exceed the speed of multiple single-field expressions in many scenarios since they will translate into a lower quantity of effective lookups.
- They will match or exceed the speed of
UNPIVOT statements depending on query complexity.
Conclusion
CROSS and OUTER APPLY can simplify many queries and provides an easier way to express many forms of logic. It can be used to express row-by-row logic and is a very useful tool for many different situations of which a few have been illustrated.
