So far in this section, we've talked about how we could join two tables together using the INNER JOIN syntax. If you notice in our database, we have three tables, so I think it would be a good thing to run through how you can actually join an unlimited number of tables together.

Now obviously the one requirement you're going to have with this is that each table has to be related to one of the other tables, and have that foreign key reference relationship.

We are going to do an INNER JOIN that joins our users, guides, and addresses all in one query. I want to look at all the data. I say select * from users u, and we are aliasing users and then say join guides g. And I want to do it on G guides users id and u.users id, and to add the new addresses table you just say join and then the name of the table and the alias as well.

Then give another "on" statement. So on an address's users id equals u users id. I'm also going to order something say order By g.Guides revenue "descending". Just so you can see how that works. I believe that's all accurate.

Let me run this and you can see we have a much larger result.

Right here we have the users, so we have our user id, name, and email. Then we have our guides and all the parameters and columns that belong to our guides, and then we have our guides quantity and then our addresses. This is a pretty large query. Now there's a couple of things to kind of taken note of right here.

Let's actually go and take a look. This is something that you have to be very careful and cognizant of when you're using inner joins, and that is that every element in an inner join is going to be included. So, in other words, our addresses, we only have a few addresses, and we only have a few guides, but both of them have duplicates.

We knew our users were going to have duplicates because a user has many guides that belong too. Also, it has many addresses and so that is something you need to keep in mind. But there are some other kinds of confusing components here, and that is because it's an inner join. We're missing some users, so if you notice let's do a quick look at our users. When I say missing some users I mean several thousand users.

Let's do select all from users but run this you'll see that we have a few thousand users here, but they were not inside of this inner join query.

That is something that you really need to be aware of when you're running inner joins especially when you're running them across multiple tables. And that is that you might have some data that can be a little bit confusing because as you can see it doesn't include all the users. It does include all of the addresses that are mapped to a user and all the guides, but there are duplicates all over the place, and this part of it can start to get a little bit messy.

Especially if you were to do something like export this to CSV and give it to an executive, they're going to wonder why in the world are all these duplicates are all over the place. There's not a lot of rhyme or reason to it. I wanted to show you that it was possible and then in the next few guides, we're going to get into different kinds of joins, joins that can help prevent this type of behavior and give you more control on the data that is actually returned in the query.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM users u
JOIN guides g
ON g.guides_users_id = u.users_id
JOIN addresses a
ON a.addresses_users_id = u.users_id
ORDER BY g.guides_revenue DESC;