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 UNPIVOT
s 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 APPLY
s 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 APPLY
s to run as if they are JOIN
s. - 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.
Michiel du Toit is a software developer based in Bloemfontein, South Africa focusing on development using C# and SQL Server (both WinForms and ASP.NET).