All right, are you ready for some more joins? Great, because in this lesson we're going to go over probably one of the most frequently used joins in SQL called the Inner Join. After this lesson, you'll know how to define and describe an inner join, explain when an Inner Join is used as well as how to pre-qualify column names to make your SQL code that much cleaner and efficient. Are you ready to get going? Great, so let's begin. So an Inner Join is used to select records that have matching values in both tables. So this is where keys become really important in tables. As you can see by the diagram here, we have table 1 and table 2, and what is going to return is only the records that are matching in both. So it's going to look for that key, in that link, and even if there's more records in table 1, it's not going to bring those back. It's just going to bring back the matches. How I start to use this though is a little bit different in terms of the syntax. So again, we're still listing out the columns that we want from both tables. So we have our select, our company name, our product name or unit price. But now instead of just saying, go and grab the data from suppliers and products, we also have to say how we want the tables to be linked. So we say, grab it from suppliers, the type of join we are going to do, Inner Join with products, and then what you're listing is really that key. So we're saying this is an Inner Join and we want you to link it on the connection between the key. So the connection between the key, the two tables, is the supplier ID in the suppliers table, and the supplier ID in the products table. So you'll notice as I say here that supplier ID equals supplier ID, but before that I've listed, I've pre-qualified which supplier ID. So I have the supplier IDs from the suppliers table that equals the supplier IDs from the products table. So one thing to note here with this prequalifying is that, if this is prequalified as suppliers and I have another name in here that is also in the same table, I want to prequalify my name in my column. So if company name is something that was listed both in products and suppliers, it wouldn't know which table to pull it from. So I would prequalify it by listing out suppliers before the company name like this, so that I would know for sure where to pull it from. Okay, so this is the Inner Join. We really just keep a lot of the same syntax in listing the two tables. What we've done differently, though, is we've added that link of what we're joining on and prequalified that. Now you can see the result that we get from this. This is now connecting only those products that had a matching supplier with them. We can though do inner joins on multiple tables. So in the last example, we just took two tables and said, which records are matching? However, we can perform this on multiple tables. However, one thing I would really be cautious against, though, is to be careful to not overly join. So joins are great and the make it super easy to blend your data together. All you need is that key. But there are taxing in the computations that they're performing. So if you don't need a join, don't join. But the good news, is there are no limits to what you can join. So to do this, you're just going to list out all the tables and define the condition. Here though, because I have multiple tables, it's really important that I'm prequalifying my names. So if you look in my from statement, I have orders and then I just list o. So I'm prequalifying the orders table as being o. I do this just because it's a little bit quicker to write out. You could just prequalify orders as orders. Some other people list out their tables where they say orders would be a, customers would be b, employees would be c. I get a little confused and don't remember sometimes the order in which I put them in. So the way I like to prequalify is the less I have to type, the better. But I also want it to usually be in a logical fashion. Just because it's a little bit easier to follow, if I don't remember which one it's coming from. So I have orders as o, customers as c, and employees as e. It's just an easy way then for me to remember. You can now see in my SELECT statement, I prequalified all the column names, and this is just something good to get into. You never know if you don't know your data that well, you don't know if the same column name resides in another table. So being really specific about which table you want it to come from is always a good idea. And it's just a nice best practice to get into. So here, I have selected o.OrderID, c.CompanyName and e for employee last name. So I'm wanting to get here the last name of the employees and the company name with the orders that were placed for this. So for this, I need to join three different tables to do that. I'm going to say from, and I have my orders with an Inner Join on customers and that's going to be joined by the customer ID. Again, you can see that I just listed out my prequalifying name with o.CustomerID and c.CustomerID. So now at this point, I have enough to get my OrderID and my CompanyName. So right now, I know at least the orders that were placed and the customers that placed them. Now I want to know the employees associated with those orders. So just after that statement, I list to do another Inner Join. This will be for the employees e table, and I'm going to join that to the orders through the EmployeeID. So I have o.EmployeeID, so that's coming the link from orders to employee, and that is equal to e.EmployeeID. So that's the EmployeeID and the employee's table. So pretty simple in terms of adding additional tables on. The thing you just want to watch out for is making sure you're prequalifying your names and making sure that you aren't doing unnecessary joins. But the thing to be really careful about is that it's easy to make a join and get data return, but really make sure you're thinking about what is the type of join you're doing. How are you connecting these records? And we'll talk a little bit later about some things you can do to double check, to make sure the number of records is a number that you were after, and the number you want. Because like we've seen in a lot of cases with using SQL, you're often able to get data back with any structured query. But we really want to make sure that it's right data that we want in the first place. Okay, so in the next few videos, to wrap up the module, we'll just be going over some more features with joins and we'll be up and running with combining multiple tables here soon. Stay tuned.
