Inner joins - SQL Tutorial

- [Instructor] Because of the nature of how data is stored in a relational database, we often will need to visit multiple tables to get all of the data that we need to answer our questions. In this example, we have surrogate keys standing in for the oil flavor, the flavor ID, and the product type, the product type ID in the product table. To get at the flavor name and the product type associated with an order, we have to add two tables to our query: the oil flavor table and the product type table, and that's where joins come in. Joins allow us to use common relationships, such as foreign key relationships, to look at data in more than one table in a single query. In this video, we're going to talk about inner joins. Using an inner join, we look for rows where there's a match in our data relationships. Here, we have two distinct data sets, and we want to compare them to find what data points that they both have in common. The overlap highlighted in this Venn diagram shows what results will come back when we use an inner join on this simple data set. So we're trying to build a query that will return these four columns from the product table and the oil flavor table. So to start, I'm going to write my select statement just as before, but I'm just going to pull from the product table. Select star, from DBO product, and I run that, and here are all the columns that are available in the product table. So now I know I need to get to the oil flavor table as well and based on the data in my result, I can probably guess that the flavor ID is going to be my most likely avenue to do that, so if I go over to the object explorer and expand the oil flavor table, look at the columns, yes, I can confirm that both of these tables do have a flavor ID column, so that's going to be my gateway to get both of these tables involved in my query. I'm going to do that using an inner join, so next to pull an oil flavor, I just write inner join, DBO oil flavor. And now I'm going to add what's known as a table alias to my query, it's just going to be an abbreviation that's going to stand in for the table name later on, it's going to make the code look a lot neater and cleaner as you'll see in just a second. So you can technically use whatever you want for your table alias, but I try to make it kind of obvious what table it's referencing, so I'll use like P for the product table, and maybe OF for oil flavor, but you can see here that the text turned blue. If you're using SSMS, that's an indicator that this is a reserved word, which means that it means something specific to SQL Server, so I can't use it in this context, so instead I'm just going to use F. Okay, so I have my two tables listed, now okay, how do I connect those things together? And I know that I use the flavor ID, so I'm going to say on my join condition is where the flavor ID and the product table, so p.flavor_id is the same as, equals, the flavor ID in the oil flavor table. And if I didn't use these aliases, I'd have to type out the whole table name here and it would just look messier than it needs to, so instead I just use aliases. So we'll run this query, and now we get all of the columns from the product table as well as all of the columns from the oil flavor table. But then you'll also notice that the flavor ID values match in each of these rows, just as I indicated in my join condition here on line nine. So now I'm only trying to get back these four columns. So instead of star, I'm going to list the column names just like I did previously. So we'll say product ID, flavor ID, flavor name, and price. So you'll notice that there's this red squiggly line under flavor ID and that tells me that there's going to be some kind of an issue with that, but let's run it and see what kind of error message we get. Ambiguous column name flavor ID, so the reason I'm getting this error is because there's two columns with the same name in different tables in my query, so I need to specify which value I'm referring to. We know that it doesn't matter which table we pull from because based on our join condition, these values have to be the same from both tables, but SQL Server doesn't know that when you're building the query, so you have to give it some more instructions. So we just would add the oil flavor table, it doesn't really matter, and then it's good practice just to add table aliases for all of these, even though technically you could get away with not doing that. Then we run this, and we get just that data that we're looking for. So when you're first starting out with the database system, it can be really tricky to know how to connect tables in your joins, and what keys are shared between which tables, and that's where ERDs can come in really handy, but if that's not available, you can also check your object explorer for foreign keys on a table. So if we look at the product table, and look at this keys sub-folder here and expand that, we can see what foreign keys are on this table and that's indicated by these white keys. And those can be good pointers for candidates that you can use in your join conditions.