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.
Как соединить 3 таблицы в sql join
There may occur some situations sometimes where data needs to be fetched from three or more tables. This article deals with two approaches to achieve it.
Example:
Creating three tables:
- student
- marks
- details
Note: Click on image if not clear to view in bigger size.
Table 1: student
Table 2: marks
Table 3: details
Two approaches to join three or more tables:
1. Using joins in sql to join the table:
The same logic is applied which is done to join 2 tables i.e. minimum number of join statements to join n tables are (n-1).
Query:
Output:
2. Using parent-child relationship:
This is rather an interesting approach. Create column X as primary key in one table and as foreign key in another table (i.e creating a parent-child relationship).
Let’s look in the tables created:
s_id is the primary key in student table and is foreign key in marks table. (student (parent) – marks(child)).
school_id is the primary key in marks table and foreign key in details table. (marks(parent) – details(child)).
Output:
Join 3 or More Tables in SQL
In this article, we will have a look at an important topic in SQL , how to perform Join when there are more than two tables given .
Typically, Joins or JOIN Clauses are used to combine rows or records from two or more tables on the basis of a common or related column between them. There are many types of Joins. It is recommended to learn about them before proceeding to have a clear idea.
To show the JOIN between 3 or more tables we will consider the following schema design with tables :
Here, we have 4 tables DEPARTMENT , GENDER , PROJECT and EMPLOYEES . This design represents relationship of one parent table with other tables. The DEPARTMENT table contains the information of each Employee’s department. The GENDER Table contains information of each employee’s gender and the PROJECT table contains the details about the project an employee is a part of including the status of project. The Employee is the main table which establishes relationships between different tables.
The EMPLOYEES table has attributes ID and name for each employee. The DEPT_ID attribute gives us information about the Employee’s department. It acts as a FOREIGN KEY which references ID (PRIMARY KEY) of DEPARTMENT Table. Similarly, GENDER_ID and PROJECT_ID are foreign keys referencing ID column of GENDER and PROJECT tables respectively.
Let us look at the description of each table along with their schema definition:
DEPARTMENT
The table DEPARTMENT is shown below:
| ID | NAME |
| 1 | OPERATIONS |
| 2 | HUMAN RESOURCES |
| 3 | FINANCE |
Let us look at how to create table DEPARTMENT and insert some records in query:
How to join three tables in SQL query – MySQL Example
Most of the time we only join two tables like Employee and Department but sometimes you may require joining more than two tables and a popular case is joining three tables in SQL.
In the case of joining three tables table, 1 relates to table 2 and then table 2 relates to table 3. If you look it closely you find that table 2 is a joining table that contains the primary key from both table 1 and table 2. As I said it can be extremely confusing to understand the join of three or more tables.
I have found that understanding table relationships as the primary key and foreign key helps to alleviate confusion than the classical matching row paradigm.
SQL Join is also a very popular topic in SQL interviews and there are always been some questions from Joins, like the difference between INNER and OUTER JOIN, SQL query with JOIN like Employee Department relationship and Difference between LEFT and RIGHT OUTER JOIN, etc. In short, this is one of the most important topics in SQL both from experience and interview points of view.
Three table JOIN syntax in SQL
Here is a nice diagram that also shows how does different types of JOINs e.g. inner, left outer, right outer and cross joins works in SQL:
SQL Query to JOIN three tables in MySQL
The primary key of the Employee table (emp_id) is a foreign key in Register and similarly, the primary key of the Department table (dept_id) is a foreign key in Register table.
Btw, the only way to master SQL join is doing as much exercise as possible. If you could solve most of SQL puzzles from Joe Celko’s classic book, SQL Puzzles, and Answers, 2nd edition, you will more confident about dealing with SQL joins, whether it could be two, three or four tables.
In order to write an SQL query to print employee name and department name alongside we need to join 3 tables. First JOIN statement will join Employee and Register and create a temporary table which will have dept_id as another column. Now second JOIN statement will join this temp table with Department table on dept_id to get the desired result.
Here is the complete SELECT SQL query example to join 3 tables and it can be extended to join more than 3 or N tables.
mysql > SELECT * FROM Employee;
+ ———+———-+———+
| emp_id | emp_name | salary |
+ ———+———-+———+
| 1 | James | 2000 |
| 2 | Jack | 4000 |
| 3 | Henry | 6000 |
| 4 | Tom | 8000 |
+ ———+———-+———+
4 rows IN SET ( 0.00 sec )
mysql > SELECT * FROM Department;
+ ———+————+
| dept_id | dept_name |
+ ———+————+
| 101 | Sales |
| 102 | Marketing |
| 103 | Finance |
+ ———+————+
3 rows IN SET ( 0.00 sec )
mysql > SELECT * FROM Register;
+ ———+———+
| emp_id | dept_id |
+ ———+———+
| 1 | 101 |
| 2 | 102 |
| 3 | 103 |
| 4 | 102 |
+ ———+———+
4 rows IN SET ( 0.00 sec )
mysql > SELECT emp_name , dept_name
FROM Employee e
JOIN Register r ON e . emp_id = r . emp_id
JOIN Department d ON r . dept_id = d . dept_id;
+ ———-+————+
| emp_name | dept_name |
+ ———-+————+
| James | Sales |
| Jack | Marketing |
| Henry | Finance |
| Tom | Marketing |
+ ———-+————+
4 rows IN SET ( 0.01 sec )
If you want to understand it even better then try joining tables step by step. So instead of joining 3 tables in one go, first join 2 tables and see how the result table will look like. That’s all on How to join three tables in one SQL query in the relational database.
By the way, in this SQL JOIN Example, we have used ANSI SQL and it will work in another relational database as well like Oracle, SQL Server, Sybase, PostgreSQL, etc. Let us know if you face any issues while running this 3 table JOIN query in any other database.
34 comments :
From long time I had difficulty joining more than two tables, as soon as number of tables increased from 2 to 3, it's started getting messy for me. This particular example of How to join three tables in SQL made my life easy. keep the good work. Can you also share some JOIN examples from other major database e.g. Oracle, SQLServer 2008 and SQL Server 2010 please
(SELECT p.`Code`, c.Name, a.Name, p.`Name`, p.`Price`, p.`Description`,p.`ImageName`, p.`Date` FROM `products` p JOIN `categories` c ON p.CID = c.ID JOIN `accessories` a ON p.AID = a.ID)
WHY MY SQL SHOW ONLY COLUMN AND IT DOESN'T SHOW ROW IN TABLE?
sir ,please write article on MySQL ans PostgreSQL Such as performance ans which one is better to follow ect
Hi all,
If I suppose that one employee can belong to severan departments,
I would look for employees who do not belong to the sales department, what will be the query?
In the organization no employee can belong to more than one department
Well explained, but beginners for more understanding try below :
SELECT e.emp_name, d.dept_name
FROM Employee e
Register r ON e.emp_id=r.emp_id
Department d ON r.dept_id=d.dept_id;
I love this example. Just one thing must be informed to the reader. Remember that when we JOIN any two tables together on any condition the resultant table contains ALL THE COLUMNs HEADINGS of the two table. KEEP this thing clear it will solve the problem when JOIN works with three or more tables.
Very well explained. Thank you. I was looking for a solution on how to join 3 tables and the explanations on other websites were not clear. This one is excellent and solved my problem.
thank you so much for providing such a gud example
nice explanation to JOIN three tables in MySQL
I get a syntax error when I execute this command
@Anonymous, what error did you get? Can you please post here?
how to join two or more different tables in same columns?
for example:table name:ledger2000,ledger2001,ledger2002,ledger2003
@Unknonwn, In order to join table you must have same keys e.g. primary key and foreign key, if you have that than you can easily join them following syntax given in this tutorial e.g.
select . from
ledger2000 L1 join ledger2001 L2 on L1.id = L2.id
join ledger2002 L3 on L2.id = L3.id
join ledger2004 L4 on L3.id = L4.id
You can use L2.id or L1.id on second join syntanx depending upon your requirement. Remember, when we join two tables a third temporary table is created which contains all columns of both tables, so you are free to use key from any of the table.
@narong kh. You have 3 columns with the same name. Try using an alias:
(SELECT
p.`Code`,
c.`Name` AS category_name,
a.`Name` AS accessory_name,
p.`Name` AS product_name,
p.`Price`,
p.`Description`,
p.`ImageName`,
p.`Date`
FROM `products` p
JOIN `categories` c ON p.CID = c.ID
JOIN `accessories` a ON p.AID = a.ID)
Be careful, if a product is not associated to any category then the JOIN will discard it. If a product is not associated to any accessory, then it will be discarded. JOIN will silently remove a row if the "ON" condition is not fulfilled for that specific row. Use "LEFT JOIN" instead of "JOIN" to see these rows (select all the columns that are used on any of the ON conditions, the rows with NULL values are the ones that are failing one or more ON conditions depending on which column in NULL).
p.CID p.AID c.ID a.ID
1 1 1 1 // All OK
2 2 NULL 2 // missing category
3 3 3 NULL // missing accessory
4 4 NULL NULL // missing both category and accessory
NULL 5 NULL 5 // product doesn't specify a category
6 NULL 6 NULL // product doesn't specify an accessory
I was always using below method for multiple join table, which quite different from all you all talking.
SELECT a.*,b.*
FROM employee a
JOIN (department b,register c)
ON (a.dept_id=b.dept_id AND b.emp_id=c.empid)
Will my statement going to have serious performance issue?
Please point me out if I am doing wrong.
it worked well, thanks a lot
Very helpful for me
Thank you so much. This is the solution that worked for my needs. I had been looking for this solution for weeks. Thank you.
Thanks alot for very nice explanation of this topic.its really very helpful to me
Well explained , nice keep it up
$rs = getRs("SELECT o.first_name, o.last_name, o.email, o.company, o.address, o.suite, o.city, o.state, o.zip, o.country, o.order_id, o.ordernumber, o.total, o.date_created, o.shipping, o.subtotal, o.total, o.discount, o.promo_discount, o.tax, o.premium_packaging, o.shipping_id, sh.shipping_name, sh.description AS shipping_description, p.promo_name, p.description AS promo_description FROM promo p RIGHT JOIN (shipping sh RIGHT JOIN orders o ON sh.shipping_id = o.shipping_id) ON p.promo_id = o.promo_id WHERE o.account_id = <$row_t['account_id']>AND ordernumber = '" . formatSql($ordernumber) . "'");
Apart from the mysql join types you have mentioned, there is also union. Union in MySQL is used to unite multiple columns from different tables into a single column in MySQL.
SELECT EMP_NAME FROM EMPLOYEE WHERE DEPARTMENT <> SALES
Can u give a perfect example for joining 3 tables
How to add where clause while joining 3 tables?
Hello @Unknown, just like you do for one table, write the WHERE clause after JOIN clause like
JOIN ON
JOIN ON
JOIN ON
WHERE