CROSS APPLY is a beautiful alternative for your FROM clause. It isn’t JOINed to anything but it can be correlated to other fields in your query. It also allows for an aggregate to be run on a row-by-row basis. CROSS APPLY can be very useful, and the following will show you what it is and how you can start using it today.

CROSS APPLY is used instead of a JOIN when you want to use a correlated query. The results of a CROSS APPLY will be applied to every row. CROSS APPLYs are perfect for when you want to include an aggregate of data (MAX, MIN, COUNT, etc.) with the results pertaining to each row. It can be correlated inside the CROSS APPLY query, making it handy to refer to specific data elements. You can also not correlate it, so that way the query results will appear on every row without having to JOIN it to something.

In this first example, I just want the number of rows in the table to appear as a field in every row. It could be done in the SELECT clause, but this is just a very simple example that can actually be very powerful when used in larger queries.

DECLARE @test TABLE (ID int IDENTITY(1,1), val CHAR(1));
INSERT INTO @test (val) VALUES
('A'),('B'),('C'),('D');
 
SELECT * FROM @test
 
SELECT *
FROM @test t
CROSS APPLY 
(
       SELECT COUNT(*) cnt FROM @test
) cnt;

The results would be as follows:

SELECT * FROM @test

SELECT * FROM @test with the CROSS APPLY added:

A correlated CROSS APPLY can give you aggregated information based on data for each row. This time, I’m going to add values A – D again, for a total of 8 rows. Notice the “mx” CROSS APPLY that has been added. This will answer the question, “What is the highest ID per val in my table?”

DECLARE @test TABLE (ID int IDENTITY(1,1), val CHAR(1));
INSERT INTO @test (val) VALUES
('A'),('B'),('C'),('D'),
('A'),('B'),('C'),('D');
 
SELECT * FROM @test
 
SELECT *
FROM @test t
CROSS APPLY 
(
       SELECT COUNT(*) cnt FROM @test
) cnt
CROSS APPLY
(
       SELECT MAX(ID) maxid FROM @test t2 WHERE t.val = t2.val
) mx;

My first SELECT * FROM @test looks just like the first one, except four more rows are added: rows 5 – 8.

My new results answer my question about the Max ID. I want to see that for A it is 5, for B it is 6, for C it is 7, and for D it is 8. In the CROSS APPLY, I am correlating on val and I am SELECTing MAX(ID). So, my results show me that MAX(ID) result for each val below.

Here’s a more practical example. Sometimes, we want to know the first or last date something happened. CROSS APPLY can help with this, as well. Building onto our current example, DATETIMEs are added to the @test table. The CROSS APPLY called fldt gets the first and last dates.

DECLARE @test TABLE (ID int IDENTITY(1,1), val CHAR(1), dt DATETIME);
INSERT INTO @test (val, dt) VALUES
('A','2018-01-04 13:04:00'),
('B','2018-01-05 15:04:00'),
('C','2018-01-06 05:33:00'),
('D','2018-01-07 08:22:00'),
('A','2018-01-08 11:02:00'),
('B','2018-01-09 12:12:00'),
('C','2018-01-10 19:05:00'),
('D','2018-01-11 21:21:00');
 
SELECT * FROM @test
 
SELECT *
FROM @test t
CROSS APPLY 
(
	SELECT COUNT(*) cnt FROM @test
) cnt
CROSS APPLY
(
	SELECT MAX(ID) maxid FROM @test t2 WHERE t.val = t2.val
) mx
CROSS APPLY
(
	SELECT MIN(dt) minDt, MAX(dt) maxDt 
	FROM @test t2 
	WHERE t.val = t2.val
) fldt;

The results are as follows. Notice for val A, the minDt is the dt value given in row 1. The maxDt is the dt value given in row 5.

While the first and last dates can be accomplished with window functions, as well, the CROSS APPLY allows you to do more with the results. Window functions are a discussion for another day.

So, that’s CROSS APPLY – stick that in your toolbox! Let me know if you have any questions or tips in the comments.