CROSS APPLY

The “CROSS APPLY” is a very important key word that can be used to simplify complex queries. It is not only simple to write, but also better in performance. To have a clear idea, let’s take two data sets as below.

CustomerID	CustomerName	Location
1	Customer1	Colombo
2	Customer2	Kandy
3	Customer3	Colombo
4	Customer4	Galle
5	Customer5	Kandy
6	Customer6	Jaffna

This record set is from a table named “Customer” and it contains six records for six customers. Below records were taken from a table called “Order” it has different orders for the above customers.

OrderID	CustomerID	OrderDate	OrderAmount
1	1	7/1/2013	120
2	1	7/2/2013	110
3	1	7/3/2013	100
4	1	7/4/2013	120
5	2	7/1/2013	20
6	2	7/2/2013	30
7	2	7/3/2013	45
8	3	7/1/2013	22
9	3	7/2/2013	65
10	3	7/3/2013	66
11	4	7/1/2013	32
12	4	7/2/2013	78
13	5	7/1/2013	36
14	5	7/2/2013	94
15	5	7/3/2013	43
16	6	7/1/2013	83
17	6	7/2/2013	53
18	6	7/3/2013	54
19	6	7/4/2013	42

Now I will execute below two queries to compare the results and execution plan. One was written with an INNER JOIN and the other was written with an CROSS APPLY key words.

SELECT *
FROM dbo.[Order] a CROSS APPLY
    dbo.Customer b
WHERE a.CustomerID = b.CustomerID

SELECT *
FROM dbo.[Order] a INNER JOIN
    dbo.Customer b ON a.CustomerID = b.CustomerID

Both the queries looks same and also not only the results, but also the execution plan is same. The execution plan can be seen below.

According to the image above, all the operators and the cost of each operator are identical. Then what is the difference?
Let’s have a look at the below query. In general terms, this will return orders with highest amount for each customer.

SELECT        a.CustomerID, CustomerName, Location, c.*
FROM            dbo.Customer AS a
    CROSS APPLY ( SELECT TOP 1 OrderID, CustomerID, OrderDate, OrderAmount
                    FROM dbo.[Order] AS b
                    WHERE a.CustomerID = b.CustomerID
                    ORDER BY OrderAmount DESC ) c

If this written, without the “CROSS APPLY” key word, we have to do little more work since the line “WHERE a.CustomerID = b.CustomerID” in above query is not supported with “INNER JOIN”.
The result of the above query looks as below which can’t be achieved with “INNER JOIN” simply.

CustomerID	CustomerName	Location	OrderID	CustomerID	OrderDate	OrderAmount
1	Customer1	Colombo	1	1	7/1/2013	120
2	Customer2	Kandy	7	2	7/3/2013	45
3	Customer3	Colombo	10	3	7/3/2013	66
4	Customer4	Galle	12	4	7/2/2013	78
5	Customer5	Kandy	14	5	7/2/2013	94
6	Customer6	Jaffna	16	6	7/1/2013	83

In summary, with the “CROSS APPLY”, the right table is evaluated for each row in left table and it is not the case with “INNER JOIN”.