TSQL Basics Part 11: CROSS APPLY – Video Explanation

This is part 11 of a 19 part series on TSQL Basics. You will first gain an understanding of the differences between each of these types of joins, and when they should be used. Then we’ll explore some common uses for joins, such as replacing a not in clause with join to get the same results with better performance. You will take home the right foundations to get at the data that you want. Make sure to check of the other parts in this series in my JOIN Types playlist.

>>> Try our JOIN Types Course Today!

Transcription:

0:07
So next we’re going to take a look at the APPLY. And this is not technically like an NC SQL standard, but it’s Microsoft’s extension. It was originally intended to be used with table valued functions, or functions that return a table as the results. And it allows you to invoke a table valued function for each row returned by an outer table expression of a query. Now, prior to really learning how the apply worked, I had found different queries that people wrote on and wrote on the internet and copied and pasted them and just wondered, what is this apply thing? And how is it working? Well, we’re gonna take a look, it’s basically like a correlated sub query, and that the values from the outside are applied, and they can be passed to the inside of the query. So let’s take a look at an example here, instead of joining from a table to another table, you’re joining from a table to a correlated sub query or a table valued function. And it’s like an INNER JOIN between a table and a function. So let’s take a look at how the CROSS APPLY looks. First, we’re going to take a look at table one. And we remember in table one, we had a list of people. And we had some favorite colors, and we had some favorite foods, we’ll just ignore the food part now, because we’re simply looking at the favorite colors. And what we’re going to do is we’re going to say select everything from table one. But instead of an INNER JOIN, or an OUTER JOIN, we’re going to say CROSS APPLY, which is going to act like an INNER JOIN. But instead of inner join into a table, we’re inner joining to a correlated sub query. And what makes this a correlated sub query is that it’s a query inside of parentheses that’s being referenced. But what correlates it is this T one favorite color value is being passed in from the outside here. So effectively, if you have, let’s say, 10 rows in table one, the sub query will be called 10 times with the parameter of that favorite color from table one being passed into it each of those 10 times bringing back a slightly different results that and JOIN those together into a table. And what it looks like is this now, this happens to be the same thing that we got when we did an INNER JOIN. But if we look at it right here, we look at an inner join. And yes, we do see the exact same thing. But it’s a way of being able to not just join to a table, but being able to join to a sub query. Now, technically, instead of a table too, you could just say INNER JOIN to that sub query and specify the join predicate, and that would work. But where this gets different is when you need to use a function. So what I’m going to do is I’m going to create a function called get favorite color multi, where it just takes in an ID, and it looks up their favorite color, and returns that favorite color. And then to test that we’ll say select star from get favorite color multi for two. And it shows that the favorite color of two is green. And if we change that to one, we would see that the favorite color was both red and Indigo because of that data inconsistency we’re dealing with of two that have an ID of one. So what we do here is that you could instead of wrapping it like this, with a select, you could instead pass in just say select from table one CROSS APPLY this function table valued function with this parameter. And for every row of table one, it’s going to call this function with this parameter. And put that all together in a nice single result set. Let’s take a look at that now. Or something simple, like looking up the color between two to two different tables that functions. There’s not a lot of value in adding that. But oftentimes we’re the sandy is when you have access to a function that someone else has provided you a table valued function. And with that, you’re not you may not have access to the tables or the data directly like some of the internal SQL functions. But you’re able to to run that and call that function multiple times and put all the results into one result set. So next, what I’m going to do is create a function and as get favorite color. I’ll just talk about that for a second. Basically, in SQL Server, there’s performance differences.
If you have a single statement table valued function, versus a multiple statement table valued function. This one is technically multiple statements, because it has this insert, and then it has a return. If instead, we created it simply as it returns a table and it was a single statement. You get different performance based off of how those how those are written. And it may not make a big difference with a table the size that we’re dealing with here. But when you get to larger tables that can have a huge performance impact in what you’re doing. But that was from a little bit more detailed presentation where we went into a lot more performance on this. But you can see that there can be different amounts of work being done, whether it’s a single statement table valued function, or a multiple statement table valued function. Okay, so we’ve looked at the CROSS APPLY. And here’s an example of using CROSS APPLY, where we’re going to select from worry stats. And this is one of those dm exact query stats, an internal system table that shows you all the different queries are running. But there is this this function called dm exact query plan that will take a plan handle and turn that into SQL code. And the way we would use that is just change this to select star. When we run it, we get everything from us as we specified there. But when we scroll over, we also get those that come from the query plan and get the plan handle. And we can see the entire plan details here that were not included in the original part. Without this CROSS APPLY function, what you would have to do is like take the results of that first query, dump it into a table somewhere, and then do a cursor somewhere, somehow iterate over the loop in order to process all those results, and then put it all together as a result where the CROSS APPLY just makes it easier to do it that way. Also, just a reminder, you can download the join types poster at SteveStedman.com. And this is a poster I created a few years ago that just sort of shows how all the different joins that we’re going to be covering through this presentation are done. It’s handy to print out and keep on your cube wall or near your workspace.

Our JOIN Types Course Comes With:

• Easy to understand lectures with examples

• Assignments

• Sample code to work with

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!