May 11, 2020 by Kenneth Fisher
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;