This is part 1 “basic 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.
>>> Try Our JOIN Types Course Today!
Transcription:
What we’re going to be using is we’re going to be using Venn diagrams. So if you can see in this screen, we have two tables, table one and table two. And they’re just shown as two circles up in the corner in green, that shows that when we select star from table one, we’re going to get everything out of table one, when I select star from table two, we’re going to get everything out of table two, there is no overlap in this example of this Venn diagram between the two tables, because they’re not connected.
This is a good time to jump over to my sample database, right here. And what we’ve got is I’ve just created a database called query training. And in there a couple of tables.
Table one, which just has an ID and a name. And then it has a foreign key column off to a favorite color table, and a favorite food table. And these are not actually foreign keys, and that there’s no foreign key relationship there. It’s just that the ID from this, it’s not an enforced foreign key, it just simple for the example.
Second table is the favorite color table. And then the third table is the favorite food table. And it’s kind of not very exciting data. But it’s a way to look at some real simple data in a way that we can use it and work on these joins. So all I’ve done is just recreated the database created the tables. Now I’m going to fill it in with some information here, no need to look at that too much. But what we’re going to do is go look at what’s in the tables.
So when we select from table one, or table two, what we get in table one is a bunch of people’s first names, there’s me, Steve, and a handful of other people. And in there, they have a link to a favorite color table. So favorite color one is over here. And it maps to the idea of one. And one of the things when we look at joins is that different things happen when you have multiple items in the other table. So this is not a like a an endorsed primary key here, which it would be nice to have. But what we ended up with is we have two colors here, red and Indigo that both map to an ID of one for instance. But then all the other colors, there’s just one color that maps to an ID rain is two and So on.
As we go through here, we’re also going to take a look at table three, which has the favorite foods in it. It’s pretty simple pizza, burger and sushi. So as we go through this, what we’re going to be doing is building queries that are based off the relationship of those three tables. And those three tables are also outlined and shown on the join type poster if you happen to download that at my website at Steve Stedman calm, but what we’re going to do is look at how do we bring the data together in different ways. So basic is select simple, we’re just pulling everything out of a table or you can filter it to a specific set.
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!