4.2 Joining Tables Together
Now that you have a couple of tables that reference each other, it's time to revisit our friend the
SELECT query. Using the concept of a
JOIN, you will be able to construct queries that will combine those tables and get back all the data you need.
1.Introduction3 lessons, 13:51
2.Working With MySQL Databases and Tables3 lessons, 16:12
3.Basic Database Read and Write Operations5 lessons, 28:11
4.Relationships Between Tables4 lessons, 28:17
5.Conclusion1 lesson, 02:50
4.2 Joining Tables Together
Now that we have some data that is linked together across multiple tables using this concept of a foreign key. We need to learn more about how we can query this data and start to pull this information together, so we can get everything that we want. So as you can see if I just do a standard select * from guest or from address I get everything that's in those individual tables. And that might be okay for now, but what if I'm generating a report or a list of all the people that are going to be joining me at this party. And I want to be able to see what's their first name, what's their last name, are there confirmed, what's their address so that I can send out some information to them. How am I gonna bring all of that together. And the answer to that is once again a select but a slightly modified select query. In this case we are going to introduce the concept of a join. Now there are many, many types of joins and we're gonna start and really only talk about the standard one. And then I'll leave a link in the description that will give you more information about where to learn more about other types of joins. But the standard one that we're gonna be talking about is called an inner join. And an inner join is a query where I'm going to select the results coming from multiple tables where a certain condition is met. And if that condition is met, I'm gonna get data back. And if it's not met, I'm going to not get data back. So let's go ahead and see what that looks like. So we're gonna start with the standard select format that we've done before. So let's start with select, and we'll do select *. And this time we're gonna say where do we want to get this data from? So we're going to say, and I'm actually gonna drop down to the next line. I'll say from guest, so I wanna get data from guest. But not just guest, I also wanna get data from address. So in order for me to do that, I have to join these two tables together. So I'm going to say join address and then from here, I need to specify how I'm going to join these things together. And I'm gonna do that with an on clause. So I'm gonna say on. And now within these parenthesis, I'm gonna say what am I using to join together? So in order for me to do that I need to reference my two tables and specify what is linking them together. And as we all ready know, we have the guest ID, foreign key pointing back to the primary key ID in guest. So the way that this going to look, is I'm going to name the table, I'm going to say guest.id = address.guest_id. And then I can close my parenthesis and then go ahead and put in my semicolon and hit Enter. So now you can see I'm getting back all of this information, which is pretty cool. So now I can see all of the data, because I used select star, I used that wild card, so basically what this query has done is it's taken all the columns from guest, all the columns from address. And based on whatever I'm using to link these two tables together in my on clause, it has now joined those two, and given me back everything, which is fine. But honestly, if I'm looking to join together two tables, or you can do this as many times over as you need. Doesn't have to be just two tables, it can be 3, 4, 5, 10 tables, whatever you want to do when you're joining these things together. But what I want to do, so I only wanna get back certain pieces of information. I don't necessarily need, to get the IDs back from my report. All I really wanna see are names, are they confirmed and their address. So let's do a slight modification here so instead now we're gonna say select and instead of select star I'm going to now specify which columns I wanna get back from which tables. So I'm gonna say from the guest table I wanna get back first_name I wanna get back guest.last_name. I wanna get back guest.confirmed. And then I also wanna get back address.address, address.city, address state and address.zip. So that's gonna be my select instead of just doing the select wildcard. Now from there I'm gonna once again say from guest join address. And this is going to be on guest.id = address.get_id. Just like that. So now when I execute this query, I'm gonna get back only the columns that I asked for. So you can see now I have my first name, last name, confirmed, address, city, state and zip. Now obviously, this is going to work, but this is very verbose and very long winded. So what I can do now is I can also take it advantage of aliases in my query. So let's just go ahead and hit Up, then we're gonna make a slight modification here. So when I'm referring to my guest table here, as well as my address table. I don't wanna have to type out full guest and address everytime I wanna use them, so in this case I'm going to use an alias. So all I need to do is give it a name after the table name. So instead of guest, I'm gonna just say I wanna refer to that as g. Address, I wanna refer to as a. So now anywhere else in here, I can get rid of all of this excess and only refer to them by g and a. So we'll come back into not only our on, but also into our select. So now I'm gonna go ahead and get rid of all this excess, it's gonna shorten up our query just a little bit, just like that. So now, we're saying that we want g, g, g, and a, a, a, based on our from, we're linking these two tables together with aliases. So I'll go ahead and hit Enter and once again I get the same result, which is good. So we're starting to clean this up a little bit. Now you can also argue that possibly some of these names that are in here are not as pretty and human-readable as you would want. So first underscore name, last underscore name, that's very techie, very computer-y. Maybe you want to make that be a little bit more human readable or pleasable to the eye. So in order to do that I can also use aliases on my select columns here. So after those I can also say selectG.first_nameas. And I wanna bring this back as first and then last_name, I wanna bring back as last. So go ahead and hit Enter now, so using aliases with the as keyword, I can now change what these columns are named. Not necessarily what their name but what they're being returned as in our query so now I'm getting this back a little bit nicer. And maybe just to be consistent, instead of having the uppercase F and L, I'll just drop those down to lowercase, like this f and l. So now we're able to make this a little bit cleaner and join these two tables together.