SQL Inner Join – How to Join 3 Tables in SQL and MySQL
Ilenia Magoni
When you’re working with your database, you might need to put together data from a few different tables. This article will show you how.
I have already written about SQL joins here and here, but let’s take a moment to review how a join works first, and particularly the syntax specific to MySQL.
SQL Join Statement
Join is a statement that lets you put together two tables, matching rows that are related to each other, and keeping only the rows that can be matched, not keeping unpaired rows.
Generic INNER JOIN statement between two tables
The SELECT . FROM statement indicates which is the first table, then the second table name is written just after the INNER JOIN keywords.
How the two tables should be joined is written in the ON statement. In this case the two tables are joined using the relationship table1.id = table2.id .
It is possible to use multiple join statements together to join more than one table at the same time.
Generic INNER JOIN statement between three tables
To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
Let’s talk a moment about the relationships you can have between tables and why you might want to join three tables together.
Relationships Between Tables in SQL
When you have tables that are related to each other, their relationships could be one of various types.
one-to-many
In a one-to-many kind of relationship, one row of the first table can be related to multiple rows of the second table.
In a relational database this can be implemented with the second table having a first_table_id column that says to which row of the first table that row is related.
many-to-one
In a many-to-one kind of relationship, one row of the first table can be related to one single row of the second table, and one row of the second table can be related to multiple rows of the first table.
In a relational database this can be implemented with the first table having a second_table_id column that says to which row of the second table that row is related.
Many-to-one
many-to-many
In this case multiple rows are related to multiple rows.
Many-to-many
This kind of relationship can’t be represent as is with SQL tables – you need to add a coupling table between the two tables so that only many-to-one and one-to-many relationships are present between tables.
Each row of the table in the middle represents one relationship between the rows of the left table and and the rows of the right table.
In practice in MySQL, that middle table will have a column for first_table_id and a column for second_table_id , with each combination being unique.
Joining SQL Tables in Practice
Let’s imagine we have an organization’s database, where we have a table with teams (their name, and other identifing info), and a table with projects (name, progress, and so on).
| id | team_name | specialty |
|---|---|---|
| 1 | Banana Throwers | Bananas |
| 2 | Wood gnawers | Gnawing on wood |
| 3 | The Pink Elephants | Stomping on the ground |
| 4 | Fluffy potatoes | Working and sleeping |
| id | project_name | progress |
|---|---|---|
| 1 | Dam building | Some more wood gnawing and ground stomping needed |
| 2 | Banana Cake | Someone is eating all the bananas |
| 3 | Sleep research | To much sleeping not enough research |
As a team can work on multiple projects, and a project can be worked on by multiple teams, there is also a third table that keeps track of team-project matches.
| project_id | group_id |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
We can use a JOIN statement to put everything together when we need to view the info from the tables in a human readable way, like this:
We choose which columns to show from each table with a SELECT statement.
We specify how the rows of the tables are to be combined with an ON statement.
And we order the rows in the way we prefer with an ORDER BY statement.
The ON statements teams.id = matches.team_id and matches.projects_id = projects.id mean that the rows are combined using the rows of the matches table. Each row of the output table has the project name and the team name combined using the pairs of project id and team id in the matches table.
The output table will look like below.
| Team_name | Project_name |
|---|---|
| Banana Throwers | Banana Cake |
| Banana Throwers | Sleep Research |
| Wood gnawers | Dam Bulding |
| Wood gnawers | Sleep Research |
| The Pink Elephants | Dam Building |
| The Pink Elephants | Dam Building |
| Fluffy potatoes | Sleep Research |
There is no column directly from the matches table. The matches table is not shown in the output but it is used as instructions for how to combine the rows of the teams and projects tables.
Conclusion
The JOIN statement lets you join together one or more tables. It has to be used in conjunction with the ON statement to determine the relationship between the rows of a table and the rows of a different table.
In this article you have learned how to use the JOIN statement to join together three different tables.
SQL Inner-join with 3 tables?
I’m trying to join 3 tables in a view; here is the situation:
I have a table that contains information of students who are applying to live on this College Campus. I have another table that lists the Hall Preferences (3 of them) for each Student. But each of these preferences are merely an ID Number, and the ID Number has a corresponding Hall Name in a third table (did not design this database. ).
Pretty much, I have INNER JOIN on the table with their preferences, and their information, the result is something like.
Where 005 would be the HallID . So Now I want to match that HallID to a third table, where this table contains a HallID and HallName .
Join 3 Tables in SQL
In SQL, you can join three tables or more by adding another join after the first one.
You can also run nested joins by specifying one join as the join condition for another.
Syntax
The most common way of joining three tables goes something like this:
This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).
You can also use nested joins by specifying one join as the join condition for another join. Like this:
Sample Data – The 3 Tables
Suppose we have the following three tables.
The Customers table:
The Cities table:
The StateProvinces table:
Example 1 – Inner Join 3 Tables
The most popular join type is the inner join, so we’ll start with that.
Here’s an example of joining the above three tables with two inner joins.
Example 2 – Combining Join Types
You can combine join types when joining three or more tables.
Here’s an example of combining an inner join with a left join.
In this case, we have a city (Peeples Valley) that doesn’t yet have any customers.
The reason we can now see that information is because the left join returns rows that have data in the left table, even if there’s no matching rows in the left table.
The previous example that combined two inner joins didn’t return this row, because inner joins discard unmatched rows from both tables. It only returns rows when there is at least one row in both tables that match the join condition.
New Sample Data – 3 Different Tables
For the remaining examples, we’ll use the following tables.
The PetTypes table:
The Owners table:
- The PetTypeId column of the Pets table is a foreign key of the PetTypeId of the PetTypes table (which is the primary key of that table).
- The OwnerId column of the Pets table is a foreign key of the OwnerId column of the Owners table.
Example 3 – Left Join 3 Tables
Let’s do a three-table join using two left joins.
Here’s an example of running two left joins against those tables.
Here, we have a pet owner that doesn’t have a pet. We can verify that by looking at the Pets.OwnerId column, and seeing that there’s no value that corresponds with Woody Eastwood’s OwnerId in the Owners table.
Example 4 – Right Join 3 Tables
The right join is the opposite of the left join. Here’s an example using the same three tables.
This time we got an extra pet type ( Rabbit ), but not the extra owner. This is because right joins return rows that have data in the right table, even if there’s no matching rows in the left table.
By the way, the reason the last PetOwner is not NULL (like last the PetName is) is because it’s a result of a string concatenation. I used the T-SQL CONCAT() function to concatenate the owner’s first and last names.
Example 5 – Full Join 3 Tables
The full join is like having a left and right join in one. It returns all rows, as long as there’s matching data in one of the tables.
This time we get a combination of the results that we got in the previous two examples.
Запрос из множества таблиц в SQL
В сегодняшней статье мы рассмотрим как выбрать данные из несвязанных таблиц с SQL и оператором JOIN. Для чего это нужно. Прежде всего, скажем, что любой запрос к сайту — это SQL запрос к базе данных, будь то Mysql, Oracle и т.д. Ответ из которой затем возращается в виде данных, оформленных как html страница. Что касается необходимости для подобного SQL запроса, он возникает из-за самой структуры базы данных, оформленной в виде отдельных таблиц. Каждая таблица содержит уникальные данные и только одну колонку с различными id для связи с другими таблицами. И чтобы получать данные из нескольких несвязанных таблиц, необходимо уметь осуществлять выборку данных путем их объединения оператором JOIN.
Мы будем использовать учебную базу по адресу: https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc . И так мы хотим осуществить выборку всех клиентов купивших продукт Тофу. Прежде всего нам понадобится схема базы данных
Мы хотим осуществить связанную выборку из таблиц Customers и таблицы Products. Но как видно из схемы данные таблицы никак не связаны напрямую, через какой-либо id. Но зато, между ними есть таблицы со сходными айдишниками.
/* выбираем клиентов и продукцию из таблицы продукты*/
SELECT Customers.ContactName, products.ProductName FROM [Products]
/*так как клиентов можно выбрать только из таблицы Customers, мы склеиваем таблицы по промежуточным
таблицам */
Join OrderDetails on products.ProductID = OrderDetails.ProductID
Join Orders on OrderDetails.OrderID = Orders.OrderID
Join Customers on Orders.CustomerID = Customers.CustomerID
/* выбираем только продукт «Tofu»*/
where ProductName = «Tofu»
В коде сверху, мы последовательно склеили таблицы по общим айдишникам, связав таблицы Customers и Products. Таким образом, с оператор JOIN языка SQL, возможно осуществлять запрос из таблиц не связанных напрямую, но имеющих промежуточные связи.
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
Она выглядит вот так:
Комментарии ( 0 ):
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.