This is part 10 “cross JOIN” of a 19 part series on TSQL Basics. You will first gain an understanding of the differences between each of these types of joins, and when they should be used. Then we’ll explore some common uses for joins, such as replacing a not in clause with join to get the same results with better performance. You will take home the right foundations to get at the data that you want. Make sure to check of the other parts in this series in my JOIN Types playlist.
>>> Try our JOIN Types Course Today!
Transcription:
Next, we’ll take a look at a CROSS JOIN. And if we look at the cross join, this is the same Venn diagram that I showed for the FULL OUTER JOIN, which shows everybody from table one and table two. But the Venn diagram doesn’t do a real great job of representing this one.
But what’s different here is that there’s no joint predicate, or the ON clause. And there’s no were being specified. So you’re simply saying, take every item in table one and match it up with every combination of items from table two. So there is no logical joining here. But this is useful. I’ve used this before, when I’ve been filling in, like test data in a reload test situation, where you put in one table you but put a bunch of first names and another table, you put middle names another table, you put last names, and then you do a CROSS JOIN, and you get every combination of all those names and quickly populate a table, we’ll take a look now at the CROSS JOIN.
And I just want to say be careful with this one. Because if you’re doing this on big tables, you can get some really gigantic results. That’s because what it’s going to do is it’s going to take the number of rows in table one, multiplied by the number of rows in table two. And that’s how many results you’re going to get. So you can see here we got 72 rows, because I think we had what, eight times nine different nominations. So we can see here that four were meet for Steve, which had a favorite color of one. It mapped me to all colors. And then for Aaron had a favorite color of three. It mapped Aaron to all colors. So it’s just a way to show a combination of everything in the two tables.
Again, this one’s super useful if you’re trying to crank out a huge amount of data to fill in a table for testing purposes.
Also, just reminder, you can download the join types poster at SteveStedman.com and this is a poster I created a few years ago that just sort of shows how all the different joins that we’re going to be covering through this presentation are done. It’s handy to print out and keep on your cube wall or near your workspace.
Our JOIN Types Course Comes With:
• Easy to understand lectures with examples
• Assignments
• Sample code to work with
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!