Weekly Tech Lesson: CROSS JOINs Versus CROSS APPLY

When we look at data, we will sometimes want to look at all data points relative to all other data points. We discussed this when we talked about CROSS APPLY, but we must remember that CROSS APPLY pertains to SQL Server only. In many SQL languages we can use CROSS JOINs. From this functionality, we can look at every record relative to every other record - for instance, if we have data for 12 months, we look at each month relative to all months (including itself). In the video, SQL Basics: How To Use A CROSS JOIN and Why, we look at anexample of using this functionality.

Note the tables that we're using and what values are identical based on the column names and what values differ based on the column names. We'll be using these tables throughout these videos. As a note on aliasing tables - the first table always gets a "t1" while the second table always gets a "t2" and so on and so forth (the 5th table would get a "t5"). Finally, consider that some SQL languages may not support the JOINs that we discuss. As a quick reminder, not all SQL languages may support the exact syntax shown. We've discussed CROSS APPLY and while this may be similar to CROSS JOIN, there are differences (I highlight these toward the end of the video), but more SQL languages support CROSS JOIN - just note that the differences. While CROSS JOIN does have the "multiple" result set that we can see in CROSS APPLY, it does not take in parameters like CROSS APPLY (as an example), so it is still restricted in use. It does however result in a multiple result set as we see.

In the example, we CROSS JOIN our two tables, which only have 2 overlapping values between tables. A large percent of the time, we'll use CROSS JOIN on a table itself, but it's useful to use CROSS JOIN on two tables because we'll sometimes do this. Also, we don't filter; for instance, if I had filtered where values equal, our result set would be different. Keep this in mind when you CROSS JOIN two tables where you need to filter strictly because of the values that you're comparing - for instance, if we want to compare a month's data to every other month, then we need to filter accordingly (for instance, on the first table we may only select one value and compare to every other value).