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;
