Join Sheeri Cabral for an in-depth discussion in this video Joining tables with matching fields using INNER JOIN, part of Up and Running with MySQL Development.
We have two tables in our Address database, people on the left and homes on the right. There is a field in our tables called Home ID that links people records to home records. So far, we have been querying MySQL using only one table at a time. However MySQL supports table joins. Let's take a look at a different type of diagram of our database. Here we are representing our tables as circles. Each circle contains the list of fields that the table has.
You can easily see that home Id is in both tables. This kind of diagram, with overlapping circles, is called a Venn diagram. If you want to see information from both tables about records that share a home Id, we have to join in the part in the middle, the shaded part. Because we are only getting records that share the part in the middle. This is called an inner join because the inner part of the circles are shaded. Let's add in a new person to our people table who has no address.
Insert into people, first name, last name, and birthday. Values, let's put in. Eli Kramer was born 1984, January 15th. And let's refresh our memory of what's in the database. So let's start from people and select star from homes. Note that there are 12 people, but only 11 people have addresses because Eli does not.
So let's get everybody's names and addresses. So first, we know we want to to get the first name, last name, and address. And we know we're going to get it from the people table. And here's where the inner join comes in. So we just write Inner Join homes. And now, we have to specify how we're connecting these two tables. So we're connecting them using the field called home Id. And there we have it. We've now joined the tables. What if the field names were different in each table? For example.
Let's alter the table, people and change the home Id field to be a different name, let's just call it, Home, and the data type was small and unsigned, default null. Now let's run the same query again and see what happens. We got an error that there's an unknown column because home Id does not exist in the people table. There's another syntax, the On syntax that we can use here instead of Using. So we can say On, and then use some dot notation, people.home. We're now using dot notation for the table.the column.
Equals homes.home Id. And here we have it. This gives us the same result as before. We talked earlier about aliasing columns, but we can also alias tables with the same as syntax, so our join clauses aren't so long. For example, we can alias people table to be as ppl and the homes table to be aliased as hme. So now, it's a little shorter.
And we get the same result. Note that one you alias a table it's just as if you renamed the table for the life of the query and you have to use the alias. So if we change this to homes but didn't change the alias. We get an error because there's no such homes table in this query we renamed it to be hme. For simplicity sake, let's change the field back in the people table to be home Id. So we're going to change the home field to be home Id. And let's go back to our basic query.
You may have guessed that if we do a select star we get all fields from both tables not just the first name, last name and address. So let's do that, let's see what happens when we do a select star. And we are going to get a very long row, so let's do \ capital g, so you'll get in vertical format. Now we have all the fields from both tables, including the home Id. What if you wanted only the first name and last name, plus all the information in the homes table? You can do that too, by changing your select clause to be select first name, last name and homes star.
Now we see first name, last name and everything in the homes table. You now know how to interjoin tables together to find records that share fields.
- Exploring MySQL data types
- Creating a database
- Creating tables
- Reading and adding data
- Using date and number functions
- Sorting results
- Inserting and replacing records
- Joining tables