Using CROSS APPLY in SQL Server

Hello everyone,

In this article I will share information about using CROSS APPLY in SQL Server

In SQL Server, CROSS APPLY is a JOIN operator that allows you to apply data from one table or query to another table or query. CROSS APPLY, like OUTER APPLY, is one of the APPLY operators in SQL Server’s T-SQL language.

In SQL Server, the CROSS APPLY operator establishes a relationship between two tables, similar to the JOIN operator. However, unlike the JOIN operator, the CROSS APPLY operator matches every record in the right table with every record in the left table. Therefore, each record in the left table is shown with the matching records in the right table.

In SQL Server, CROSS APPLY is especially useful when using a subquery or function that depends on each row of a table. For example, CROSS APPLY can be used in a scenario where each row in a table needs to be subjected to a calculation or transformation. To perform this operation, a query is written to retrieve each row in a table and that query is applied to another table.

In SQL Server, the CROSS APPLY operator applies data from one table to use it in another table or query. This operator works in a similar way to the JOIN operator, but it maps each record to every record in the other table. Therefore, CROSS APPLY is especially useful when performing operations that are related to each row of a table.

In SQL Server, CROSS APPLY is a powerful operator that can generally be used to join a table with a table-valued expression or a user-defined function that returns a table. It allows you to perform complex calculations, summarize data, and join tables more efficiently.

To understand CROSS APPLY, let’s create the following table and examine the query.

CREATE TABLE Musteriler (
	MusteriID INT PRIMARY KEY
   ,MusteriAdi NVARCHAR(50)
   ,MusteriSoyadi NVARCHAR(50)
   ,Sehir NVARCHAR(50)
);

CREATE TABLE Siparisler (
	SiparisID INT PRIMARY KEY
   ,MusteriID INT
   ,SiparisTarihi DATE
   ,FOREIGN KEY (MusteriID) REFERENCES Musteriler (MusteriID)
);


INSERT INTO Musteriler (MusteriID, MusteriAdi, MusteriSoyadi, Sehir)
	VALUES (1, 'Ali', 'Yilmaz', 'Istanbul'),
	(2, 'Ayse', 'Demir', 'Ankara'),
	(3, 'Mehmet', 'Ozturk', 'Istanbul'),
	(4, 'Zeynep', 'Ozkan', 'Izmir'),
	(5, 'Mustafa', 'Gunes', 'Ankara');

INSERT INTO Siparisler (SiparisID, MusteriID, SiparisTarihi)
	VALUES (1, 1, '2022-03-01'),
	(2, 1, '2022-03-05'),
	(3, 2, '2022-03-07'),
	(4, 4, '2022-03-10'),
	(5, 3, '2022-03-12'),
	(6, 2, '2022-03-15'),
	(7, 1, '2022-03-17'),
	(8, 5, '2022-03-20');

SELECT
	M.MusteriAdi
   ,M.MusteriSoyadi
   ,S.SiparisTarihi
FROM Musteriler AS M
CROSS APPLY (SELECT TOP 2
		*
	FROM Siparisler AS S
	WHERE S.MusteriID = M.MusteriID
	ORDER BY SiparisTarihi DESC) AS S;

Above, the query will list the last two orders for each customer in the Customers table. The CROSS APPLY operator uses a subquery to determine the last two orders for each customer. This subquery retrieves the last two orders for the customer from the Orders table and sorts them in reverse order by date. Then, the CROSS APPLY operator joins this subquery with each customer in the Customers table, resulting in a list of the last two orders for each customer.

The above query may sound a bit complicated. We can overcome this by using a function that returns a table. First, let’s create the following function.

CREATE FUNCTION dbo.fnGetOrders (@musteriID INT)
RETURNS TABLE
AS
	RETURN
	(
	SELECT TOP 2
		SiparisID
	   ,MusteriID
	   ,SiparisTarihi
	FROM Siparisler
	WHERE MusteriID = @musteriID
	ORDER BY SiparisTarihi DESC
	);

Now let’s use this function together with CROSS APPLY.

SELECT
	M.MusteriAdi
   ,M.MusteriSoyadi
   ,S.SiparisTarihi
FROM Musteriler AS M
CROSS APPLY dbo.fnGetOrders(M.MusteriID) AS S;

The query above will also list the last two orders for each customer in the Customers table. The CROSS APPLY operator returns the results by calling the dbo.fnGetOrders function for each customer. This function retrieves the last two orders for the customer from the Orders table and sorts them in reverse order by date. Then, the CROSS APPLY operator joins this function with each customer in the Customers table, and the result lists the last two orders for each customer.

In SQL Server, CROSS APPLY is a JOIN operator that allows processing each record from one table through a transaction or subquery that has a dependency on another table. CROSS APPLY is often used to further process or extend the main query result set by using a subquery within a query. This subquery is run separately for each record by the CROSS APPLY operator and its results are returned to the main query.

In SQL Server, the CROSS APPLY operator is similar to the INNER JOIN and OUTER JOIN operators, but unlike these operations, it can have a higher cost in terms of performance because a subquery must be run for each record. You can analyze cost and performance by examining the performance of your query results.

In SQL Server, CROSS APPLY allows complex queries to be written that are dependent from one table to another table. For example, it can be used when each record in one table needs to be joined with one or more related records in another table. It can also involve the use of functions, such as functions used in subqueries.

In SQL Server, CROSS APPLY is often used to handle complex structures created in database design and is especially effective when working on large datasets.

Good luck to everyone in business and life.