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 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 times we only join two tables like Employee and Department but some
time you may require to join more than two tables and a popular case is joining
three tables in SQL. In case of joining three tables table 1 relates to table 2
and than 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 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
is always been some questions from Join like 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 topic in
SQL both from experience and interview point of view.

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

SQL Query to JOIN three tables in MySQL

In order to better understand joining
of 3 tables in SQL query let's see an example.  Consider popular example of Employee and Department schema. I
our case we have used a link table called Register which link
or relate both Employee to Department. Primary
key of Employee table (emp_id) is foriegn
key in Register and similarly primary key of Department table (dept_id) is foreign
key in Register table.

In order to write an SQL query to
print employee name and department name along side 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
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 more 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 relational database. By the way in this SQL JOIN Example we have used ANSI SQL and it will work in other 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.

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

Source:http://javarevisited.blogspot.com/2012/11/how-to-join-three-tables-in-sql-query-mysql-sqlserver.html