CROSS APPLY operator invokes/executes a Table Valued User Defined Function for each row returned by the LEFT side table expression of the CROSS APPLY operator. It returns only those rows from the LEFT side table expression of the CROSS APPLY operator which produces the result from the RIGHT side Table Valued User Defined function/Table expression. So from the result perspective we can say that the CROSS APPLY is similar to that of the classic INNER JOIN.

To understand CROSS APPLY in Sql Server with extensive list of examples, let us create the demo tables with sample data as shown in the following image by executing the following script:

CREATE DATABASE SqlHintsCROSSAPPLYDemo
GO
USE SqlHintsCROSSAPPLYDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) 
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders
(OrderId INT, CustomerId INT,Amount MONEY, OrderDate DATETIME)
GO
INSERT INTO Orders(OrderId, CustomerId,Amount,OrderDate) 
VALUES(100,1,100.0,Getdate()-30), (101,1,200.0,Getdate()-20),
      (103,1,300.0,Getdate()-10), (104,2,150.0,Getdate())	
GO

Create a Table Valued Function

Now execute the following script to create an inline Table valued function fnGetLastTwoCustomerOrders, which returns order details of the last 2 orders corresponding to the input customer id.

CREATE FUNCTION dbo.fnGetLastTwoCustomerOrders
( @CustomerId AS INT )  
RETURNS TABLE 
AS 
RETURN ( 
		SELECT TOP 2 * 
		FROM Orders O 
		WHERE O.CustomerId = @CustomerId 
		ORDER BY OrderDate DESC
	)

Let us verify this functions result for the customer id = 1

--Return last two order details for the customer id = 1
SELECT * FROM dbo.fnGetLastTwoCustomerOrders(1) O
GO

RESULT:

CROSS APPLY Examples

Example 1: Demonstrates how we can re-write a very basic INNER JOIN query by CROSS APPLY operator

CROSS APPLY is not an alternative for INNER JOIN, instead each of them have their own pros and cons. The general rule of thumb is you may like to use the CROSS JOIN if you want to evaluate/execute the RIGHT side table expression or Table Valued function for each row in the LEFT side table expression. Just to demonstrates how we can use CROSS APPLY operator with a very basic example, here in this example will re-write a very basic INNER JOIN query by CROSS APPLY.

We can write a query like below by using INNER JOIN to get the details of the Customers who have orders in the Orders table.

SELECT *
FROM Customers C
        INNER JOIN Orders O
	   ON C.CustomerId = O.CustomerId

RESULT

We can re-write the above INNER JOIN query using CROSS APPLY as shown below and still get the same result

SELECT *
FROM Customers C
  CROSS APPLY (SELECT * 
               FROM Orders O
     	       WHERE O.CustomerId = C.CustomerId) ORD

RESULT

From the above results we can see that the re-written CROSS APPLY query is returning the same result as that of the INNER JOIN query.

[ALSO READ] OUTER APPLY in Sql Server

Example 2: CROSS APPLY operator between a Table and user defined Table Valued Function

We can write a query like below using CROSS APPLY operator to get details of all the customers with their last two order details. This query basically evaluates the function fnGetLastTwoCustomerOrders for each row in the Customer table by taking CustomerId as the input.

SELECT *
FROM dbo.Customers C
      CROSS APPLY 
        dbo.fnGetLastTwoCustomerOrders(C.CustomerId) O

RESULT:

From the result we can see that for the customer with customer id =1, we have two orders in the result even though this customer has three orders because the function fnGetLastTwoCustomerOrders returns at max the last two orders for any given CustomerId. And for CustomerId =2, we see only one order detail in the result as this customer has placed only one order till now and the details of the Customer with CustomerId = 3 is not available in the result because the function fnGetLastTwoCustomerOrders doesn’t return any record as this this customer is yet to place any order.

[ALSO READ] Sql Server APPLY operator

Example 3: CROSS APPLY operator between a Table and Table Expression

In the example 2 the RIGHT side of the CROSS APPLY operator was User Defined Function, this example demonstrates how we can have a Table Expression on the right side of this operator.

The below script can also achieve the same result as that of example 2, but here we have not used the Table Valued Function fnGetLastTwoCustomerOrders instead we have used the derived table.

SELECT *
FROM dbo.Customers C
	CROSS APPLY (SELECT TOP 2 * 
	  	     FROM Orders O 
		     WHERE O.CustomerId = C.CustomerId 
		     ORDER BY OrderDate DESC) ORD

RESULT: