SQL Q&A
Python Java C++ C C# PHP R SQL DS Algo InterviewQ

Join Three or More Tables in SQL



To join three or more table, simply repeat the JOIN clause repeatedly. Please refer to the syntax below, where INNER JOIN clause is used to join three tables. Any other JOIN type can be used in the same way. Along with this, it can be used to join any number of tables.

Syntax

The syntax for using RIGHT JOIN keyword is given below:

SELECT table1.column1, table1.column2, ... 
  table2.column1, table2.column2, ...
  table3.column1, table3.column2, ...
FROM table1 INNER JOIN table2
  ON table1.matching_column = table2.matching_column
INNER JOIN table3
  ON table1.matching_column = table3.matching_column;

Example:

Consider a database tables called Employee, Bonus_Paid and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Bonus_Paid table

EmpIDBonus
1500
2400
3450
4550
5400
6600

Table 3: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80XXXXX0002XXX, Brooklyn, New York, USAF
+33-14XXXXX013XXX, Grenelle, Paris, FranceM
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, NetherlandsF
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, ChinaM
+65-67XXXXX47XXX, Yishun, SingaporeM
+81-35XXXXX728XXX, Koto City, Tokyo, JapanM

  • To fetch a record containing EmpID, Name, Bonus and Address from these tables, the following SQL query can be used:

    SELECT Employee.EmpID, Employee.Name, 
      Bonus_Paid.Bonus, Contact_Info.Address
    FROM Employee INNER JOIN Bonus_Paid
      ON Employee.EmpID = Bonus_Paid.EmpID
    INNER JOIN Contact_Info
      ON Employee.EmpID = Contact_Info.EmpID;
    

    This will produce the result as shown below:

    EmpIDNameBonusAddress
    2Marry400XXX, Brooklyn, New York, USA
    3Jo450XXX, Grenelle, Paris, France
    4Kim550XXX, Geuzenveld, Amsterdam, Netherlands
    6Huang600XXX, Yizhuangzhen, Beijing, China
  • To fetch the above record but with a condition like Bonus should be greater than equal to 500, the following SQL query can be used:

    SELECT Employee.EmpID, Employee.Name, 
      Bonus_Paid.Bonus, Contact_Info.Gender, 
      Contact_Info.Address
    FROM Employee INNER JOIN Bonus_Paid
      ON Employee.EmpID = Bonus_Paid.EmpID
    INNER JOIN Contact_Info
      ON Employee.EmpID = Contact_Info.EmpID
    WHERE Bonus_Paid.Bonus >= 500;
    

    This result of the following code will be:

    EmpIDNameBonusAddress
    4Kim550XXX, Geuzenveld, Amsterdam, Netherlands
    6Huang600XXX, Yizhuangzhen, Beijing, China

5