## 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.