Explain CROSS APPLY like I’m 5.

5

May 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;

5 thoughts on “Explain CROSS APPLY like I’m 5.

  1. Chris Harshman says:

    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. 🙂

  2. Pail Writer says:

    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. .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,152 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: