SQL Server - SQL CROSS APPLY Statement

Using CROSS APPLY In SQL SELECT Statements

In this article I  take you through the following:

• CROSS APPLY
• OUTER APPLY
• CROSS APPLY with User Defined Table Valued Functions

When Should We Use The CROSS APPLY Statement

The CROSS APPLY statement behaves in a similar fashion to a correlated subquery, but allows us to use ORDER BY statements within the subquery. This is very useful where we wish to extract the top record from a sub query to use in an outer sub query.

CROSS APPLY is also used when we wish to pass values into a User Defined Table Valued Function.

CROSS APPLY

The following example produces a list of orders along with the number of days between the order and the next order placed by the same customer.

USE Northwind	
GO

SELECT o1.OrderID, o1.OrderDate, ca.OrderID AS NextOrder, 
		ca.OrderDate AS NextOrderDate, CustomerID, 
			DATEDIFF(DAY, o1.OrderDate,ca.OrderDate) DaysToNextOrder
FROM Orders AS o1
	CROSS APPLY
			(SELECT TOP 1 o.OrderDate, o.OrderID
			 FROM Orders AS o 
                         WHERE o.customerID = o1.customerID
				AND o.OrderID > o1.OrderID
			 ORDER BY OrderID) AS ca

ORDER BY CustomerID, o1.OrderID

CROSS APPLY enables us to join the order records (Orders AS o1) to the subquery (derived table named as ca), but we can also use an ORDER BY in the subquery to sort the order records in the subquery in ascending order of orderdate to enable us to identify the first order (TOP 1) after the current record’s orderdate.

OUTER APPLY

The OUTER APPLY statement behaves like an OUTER JOIN.

The following example will retain all orders from the outer query (Orders AS o1) even if there are no subsequent orders. The previous version will only display orders that have subsequent orders.

-- Days Between Orders - Include orders where there is no further order
USE Northwind
GO
SELECT o1.OrderID, o1.OrderDate, ca.OrderID AS NextOrder, 
		ca.OrderDate AS NextOrderDate, CustomerID, 
			DATEDIFF(DAY, o1.OrderDate,ca.OrderDate) DaysToNextOrder
FROM Orders AS o1
	OUTER APPLY
			(SELECT TOP 1 o.OrderDate, o.OrderID
			 FROM Orders AS o 
 WHERE o.customerID = o1.customerID
				AND o.OrderID > o1.OrderID
			 ORDER BY OrderID) AS ca
ORDER BY CustomerID, o1.OrderID

CROSS APPLY and Table Valued Functions

Table Valued Functions return a set of records as output. If we simply wish to view all records returned for a given input value we can call a user defined function as follows:

USE AdventureWorks2014
GO

SELECT *
FROM [dbo].[ufnGetContactInformation] (5454)

If, however, we wish to pass a value into the function for each record in another table we need to join the user defined function and the table. This requires a CROSS APPLY statement as we cannot use an INNER JOIN with an ON clause – the join value needs to be passed into the function.

See the next example.

USE AdventureWorks2014
GO

SELECT AccountNumber,  ci.*
FROM Sales.Customer
	CROSS APPLY [dbo].[ufnGetContactInformation] (PersonID) AS ci

To include all customer records, even those that do not have associated contact information (stores, for example) use an OUTER APPLY:

USE AdventureWorks2014
GO

SELECT AccountNumber,  ci.*
FROM Sales.Customer
	OUTER APPLY [dbo].[ufnGetContactInformation] (PersonID) AS ci

SQL Server Database Querying

Advanced SQL Server Database Querying