T-SQL: How To Write A Cross Apply and Why
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?
For mastering data transformation from one form to another form, check out the highest-rated Automating ETL course on Udemy. For a coupon to the course, check out the trailer video on the channel SQL In Six Minutes.
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.
Are you looking for tech consultants that can assist with design and development? From building custom applications to working with existing infrastructure that's causing you trouble, we can get you connected to consultants who can assist. You can contact for assistance.