CROSS vs OUTER APPLY

2

June 10, 2021 by Kenneth Fisher

I love CROSS APPLY. I also love OUTER APPLY. What’s the difference though? The same difference as with an INNER vs OUTER JOINs. The CROSS APPLY only includes rows where there is a match, while OUTER APPLY includes all rows even if there isn’t a match. I’ve found over time that I have a lot easier time using an example for this rather than trying to explain in any detail. I’m going to use STRING_SPLIT for my example because it’s easy.

CREATE TABLE #ApplyTest (Id int NOT NULL IDENTITY (1,1), CSVList varchar(50));
INSERT INTO #ApplyTest VALUES ('1,2,3'),('a,b,c'),(NULL), ('1,a,b,c');

I’ve left the 3rd entry for the column CSVList as NULL. This means that the output of STRING_SPLIT for that row is going to be empty.


CROSS APPLY

SELECT *
FROM #ApplyTest
CROSS APPLY string_split(#ApplyTest.CSVList,',') ListValue;

You’ll notice that Id 3 is missing. Just like with an INNER JOIN the CROSS APPLY only returns data where there are values from both tables.


OUTER APPLY

SELECT *
FROM #ApplyTest
OUTER APPLY string_split(#ApplyTest.CSVList,',') ListValue;

This time Id 3 shows up with a NULL value, for, well, the value column. Wow that was an awkward sentence. Probably should have aliased that column. Regardless, you can see that the table valued function (TVF) STRING_SPLIT is treated as the OUTER part of the JOIN. We get data for #ApplyTest and nothing from the STRING_SPLIT

2 thoughts on “CROSS vs OUTER APPLY

  1. […] Kenneth Fisher takes us through CROSS versus OUTER APPLY: […]

  2. […] the other hand my naming skills for the OUTER APPLYs are exceptional […]

Leave a Reply to DDL Trigger to log database level security commands. | SQL Studies Cancel 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,580 other followers

Follow me on Twitter

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