Thursday, November 22, 2012

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

How to join three tables in SQL with Example - MySQL Oracle SQL ServerIn 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

10 comments :

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

narong kh 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)
WHY MY SQL SHOW ONLY COLUMN AND IT DOESN'T SHOW ROW IN TABLE?

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?

Emmanuel Ladislaus 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

JOIN

Register r ON e.emp_id=r.emp_id

JOIN

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

Thanks it proved very helpful to me!!!!!!!!!

Anonymous said...

excellent one i love it because it explains every thing to me....

Post a Comment