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.
Some questions that are answered in the video:
- 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).
- As a quick reminder, 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.
- Similar to joins, notice how the condition that we specify affects the results.
- When we use CROSS APPLY with our self-join table, what is the result? How could this result be useful in situations where we want to see this with data sets?
- Considering what we've learned previous with INNER JOINs, LEFT JOINs, and SELF JOINs, where may CROSS APPLY be used inappropriately?
Results of all data per row of data can be extremely useful because there are situations where we may want to look at data in this way. What is key is that we ensure that's the outcome we want and we also ensure that we've constructed our tables in a manner that gets us the best performance.
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.
Top comments (0)