CROSS vs OUTER APPLY

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