How to join three tables in SQL query – MySQL Example

Three table JOIN Example SQL
Joining three tables in single SQL query can be very tricky if you are not good with the concept of SQL Join. SQL Joins have always been tricky not only for new programmers but for many others,  who are in programming and SQL for more than 2 to 3 years. There are enough to confuse someone on SQL JOIN ranging from various types of SQL JOIN like INNER and OUTER join, LEFT and RIGHT outer join, CROSS join etc. Between all of these fundamentals, What is most important about Join is, combining multiple tables. If you need data from multiple tables in one SELECT query you need to use either subquery or JOIN. Most of the times 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 at closely you find that table 2 is a joining table which contains primary key from both table 1 and table 2. As I said it can be extremely confusing to understand join of three or more tables.

I have found that understanding table relationship 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 e.g. 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 point of view.

But, if you are new to SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

Three table JOIN syntax in SQL

Here is a general SQL query syntax to join three or more table. This SQL query should work in all major relation database e.g. MySQL, Oracle, Microsoft SQLServer, Sybase, and PostgreSQL:

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
                                  join table3 ON table2.primarykey = table3.foreignkey

We first join table 1 and table 2 which produce a temporary table with combined data from table1 and table2,  which is then joined to table3. This formula can be extended to more than 3 tables to N tables, You just need to make sure that SQL query should have N-1 join statement in order to join N tables. like for joining two tables we require 1 join statement and for joining 3 tables we need 2 join statement.

Here is a nice diagram which also shows how does different types of JOINs e.g. inner, left outer, right outer and cross joins works in SQL:

How to join three tables in SQL with MySQL database example

SQL Query to JOIN three tables in MySQL

How to join three tables in SQL with Example - MySQL Oracle SQL ServerIn order to better understand the joining of 3 tables in the SQL query let's see an example.  Consider the popular example of Employee and Department schema. In our case, we have used a link table called Register which links or relates both Employee to Department. 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 than 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 e.g. Oracle, SQL Server, Sybase, PostgreSQL etc. Let us know if you face any issue while running this 3 table JOIN query in any other database.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Other SQL Interview Questions articles for preparation
List of frequently used MySQL Server commands
10 popular SQL queries from Interviews

Thanks for reading this article so far, if you like this article then please share with your friends and colleagues. If you have any questions, suggestions or doubt then please drop a comment and I'll try to answer your question. 


Tim said...

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

Unknown said...

(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)

khawaraleem said...

sir ,please write article on MySQL ans PostgreSQL Such as performance ans which one is better to follow ect

Rubens said...

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?

Unknown said...

In the organization no employee can belong to more than one department

Pritam said...

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;

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

thank you so much for providing such a gud example

Anonymous said...

nice explanation to JOIN three tables in MySQL

Anonymous said...

I get a syntax error when I execute this command

javin paul said...

@Anonymous, what error did you get? Can you please post here?

Unknown said...

how to join two or more different tables in same columns?
for example:table name:ledger2000,ledger2001,ledger2002,ledger2003

javin paul said...

@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 =
join ledger2002 L3 on =
join ledger2004 L4 on =

You can use or 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.

Unknown said...

@narong kh. You have 3 columns with the same name. Try using an alias:

c.`Name` AS category_name,
a.`Name` AS accessory_name,
p.`Name` AS product_name,
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

Anonymous said...

Hi all,

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.

Anonymous said...

it worked well, thanks a lot

Unknown said...

Very helpful for me

Travis said...

Thank you so much. This is the solution that worked for my needs. I had been looking for this solution for weeks. Thank you.

Anonymous said...

Thanks alot for very nice explanation of this topic.its really very helpful to me

Anonymous said...

Do it yourself

Unknown said...

Well explained , nice keep it up

shivanaidu said...


Anonymous said...

$rs = getRs("SELECT o.first_name, o.last_name,,, o.address, o.suite,, o.state,,, o.order_id, o.ordernumber,, o.date_created, o.shipping, o.subtotal,,, o.promo_discount,, 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) . "'");

oliver said...

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.

Anonymous said...


Unknown said...

Can u give a perfect example for joining 3 tables

Anjali said...

How to add where clause while joining 3 tables?

javin paul said...

Hello @Unknown, just like you do for one table, write the WHERE clause after JOIN clause like

Anonymous said...

This is Right..
Very helpful Query for me

javin paul said...

Thanks @Anonymous, I am happy that you find this query useful.

Gopal Kumar said...

p.CID=c.ID never work. It should be p.CID=c.CID or p.ID=c.ID. Field name should be same.

javin paul said...

Hello @Gopal, very well spotted, join column name must be same.

Post a Comment