Explain CROSS APPLY like I’m 5.
5May 11, 2020 by Kenneth Fisher
A good friend of mine (Randolph West (blog|twitter) asked that someone Explain CROSS APPLY like they are 5. So, here’s my attempt. No promises on the 5 but I’m going to at least aim for 10.
Everyone understands an INNER JOIN right? Using a very simplistic example you take TableA and TableB and based on a condition (say TableA.col1 = TableB.col1) and any time a row in TableA has a match in TableB grab the information from both and throw them (carefully, we don’t want to break anything) into the result set.
So how about CROSS APPLY? In this case for every row in TableA you apply a function or calculation. The simplest example I can think of is using STRING_SPLIT.
-- Demo table
CREATE TABLE #Strings (col1 nvarchar(500));
INSERT INTO #Strings VALUES ('abc,def'), ('xyz,lmn'), ('abc');
So we want to split out the comma delimited strings in the above table, one row per value. The easiest way to do this is to use the STRING_SPLIT function, but it’s a table valued function (meaning it returns a table) so we can’t just do this:
SELECT #Strings.col1, string_split(#Strings.col1,',') FROM #Strings;
Instead we have to use CROSS APPLY to (again) apply it to each row. And again any time there is a result from the function or calculation, it and the information from TableA are tossed into the result set.
SELECT #Strings.col1, String_Pieces.value FROM #Strings CROSS APPLY string_split(col1,',') AS String_Pieces;
SQL Studies 
Imagine making a sandwich, that’s like doing a join. We join one selected slice of bread (but not the heels, 5 year olds don’t like the heel) to peanut butter. Then join another selected slice of bread to the jelly. Join those two slices of bread together. (peanut butter and jelly sides together of course)
Now think of chewing as a function. Each bite of the sandwich you take, you are cross applying the bread, peanut butter, and jelly to your mouth to begin the chewing function. The result set is a full belly. 🙂
Ha! Very nice!
Is there a SQL Server 2012 alternative to the “string_split” function? I tried running the code in the samples and received this error message:
Msg 195, Level 15, State 10, Line 15
‘string_split’ is not a recognized built-in function name
Thanks. .
No, you have to do it manually. Aaron talks about different ways to do it and what’s faster here:
https://sqlperformance.com/2016/03/sql-server-2016/string-split
Thank you. Most helpful. I will be glad when my company comes into the 19th century and upgrades to at least SQL Server 2016.