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.
Some questions that are answered in the video:
- 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).
- CROSS JOIN, unlike CROSS APPLY, is supported in more SQL languages than SQL Server. While it 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.
- Consider scenarios where we may want to use this functionality
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.
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). 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.