CROSS vs OUTER: Knockdown drag-out free for all

In this corner, with a total of 10 letters – we have CROSS APPLY. In the other corner, also with a total of 10 letters – we have OUTER APPLY.

Yes, that’s right. From the outside, there’s truly not a lot of difference between the two apply operators.

Both of them work very well with SQL functions.

But, internally, there is a decent amount of difference between the two.

First, let’s do a few changes to things to really show off the difference. We’re going to mess with our long-ago used dbo.Person1 and dbo.Person2 table – found here.

Because our wonderful business partners have requested that we combine those persons in dbo.Person1 with the dbo.Person2 table based on P2’s first letter of the last name and P1’s first letter of their first name. The other thing that matters is that both tables have to have an exact match.

SELECT *
FROM dbo.Person1 P
CROSS APPLY (SELECT TOP 1 P.ID
	FROM dbo.Person2 P2
	WHERE LEFT(P2.LastName, 1) = LEFT(P.FirstName, 1)
	ORDER BY NEWID() DESC) Z

Now, I threw the ORDER BY clause in there because I want to see something different each time, but if you want to see the same old data each and every time, you go for it.

I love the fact that the CROSS APPLY allows me to see where the data is in both tables. On the other hand, OUTER APPLY allows me to see where the data is in only one of the tables. See the example below:

SELECT *
FROM dbo.Person1 P
OUTER APPLY (SELECT TOP 1 P.ID
	FROM dbo.Person2 P2
	WHERE LEFT(P2.LastName, 1) = LEFT(P.FirstName, 1)
	ORDER BY NEWID() DESC) Z

I’ll leave it to you – the frenzied reader – to determine which patients are showing in the OUTER APPLY that are not in the CROSS APPLY.

And with that, my dear friends, I must leave you now – as my editor is already sad because of the number of words I’ve written with this post. Until next time!