Weekly Tech Lesson: How To Write A Cross Apply (Platform Specific)

In our series, we've looked at how we can match data using relationships, or find data that don't match based on conditions, and even compare data to itself. What if we wanted to look at a data point relative to every other data point in a data set? While we have already discussed ways in which we could do this that might take extra effort, one way in which we could accomplish this is the CROSS APPLY functionality. This functionality allows us to bypass any iterative approach with loops so that we can run a comparison based on the condition that we specify. In the video, SQL Basics: How To Use A CROSS APPLY and Why we look at using the CROSS APPLY functionality.

For this video, we use a self join table since our join will only involve one table. Since we join the table onto itself, any column that we use in the join condition will match provided that we don't change any part of the condition (which we do in our example and I show why). We'll also remember that not all SQL languages may support the exact syntax shown. Different SQL languages may have different approaches to solving the same problem or developing the same solution. Like with joins, how we write the conditions for the CROSS APPLY affects the results, along with what we choose to select for the final output. What we see with our self join table is that the results are compounded based on the multiples; this can be extremely useful when we want to look at every data point relative to every other data point. While we can accomplish data point comparisons involving previous and next data with self-joins, CROSS APPLY allows us to do this with every data point, not just the previous and next data points. We can also save time here because we don't have to write an loop logic to run a comparison like this.

One of my favorite examples of CROSS APPLY being used (and a common query that I regularly return to) is:

SELECT session_id SessionID
    , [text] QueryText
FROM sys.dm_exec_requests  
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)

This gets us the actual SQL query text being run for every transaction and this happens because in the CROSS APPLY we're passing in the sql_handle. Another example of CROSS APPLY being useful in business is when we want to compare a timeframe's performance (ie: monthly) to every other relative timeframe's performance - so the sales' numbers of a particular month relative to every other month's sales numbers.

For more SQL lessons, you can continue to watch the SQL Basics series.