How to join three tables in SQL query – MySQL Example (original) (raw)

Three tables JOIN Example SQL

Joining three tables in a 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 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 general SQL query syntax to join three or more tables. This SQL query should work in all major relational databases like 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 the SQL query should have N-1 join statement in order to join N tables. for joining two tables, we require 1 join statement and for joining 3 tables we need 2 join statements.

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

In 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 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.

Other SQL Interview Questions articles for preparation

• What is difference between correlated and noncorrelated subqueries in SQL
• Difference between clustered and nonclustered index in SQL
• What is ACID properties of transaction in a database
• When to use truncate over delete in SQL query
• 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 it with your friends and colleagues. If you have any questions, suggestions, or doubts then please drop a comment and I'll try to answer your question.