SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - INNER JOIN



The SQL Server (Transact-SQL) INNER JOIN keyword (or sometimes called simple JOIN) is used to combine column values of two tables and returns all rows from both of the tables when there is a match between the columns.

SQL Server INNER JOIN

Syntax

The syntax for using INNER JOIN keyword in SQL Server (Transact-SQL) is given below:

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

Example:

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

Table 1: Employee table

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

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • To inner join Employee and Contact_Info tables based on matching column EmpID, the query is given below. This will fetch Name and Age columns from Employee table and Address column from Contact_Info table.

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

    This will produce the result as shown below:

    NameAgeAddress
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Huang28Yizhuangzhen, Beijing, China
  • To fetch all fields of a table, table.* keyword is used, for example - to fetch all fields of the Employee table, Employee.* is used in the below query:

    SELECT Employee.*, Contact_Info.Address 
    FROM Employee
    INNER JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID;
    

    This result of the following code will be:

    EmpIDNameCityAgeSalaryAddress
    2MarryNew York242750Brooklyn, New York, USA
    3JoParis272800Grenelle, Paris, France
    4KimAmsterdam303100Geuzenveld, Amsterdam, Netherlands
    6HuangBeijing282800Yizhuangzhen, Beijing, China

Join Three Tables

To join three tables, simply repeat the JOIN clause repeatedly. See the syntax below, where INNER JOIN clause is used to join three tables. Any other JOIN type can be used in the same way. Similarly, it can be used to join any number of tables.

Syntax

The syntax for joining three tables using INNER JOIN keyword in SQL Server (Transact-SQL) 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 containing 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-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • To fetch a record containing EmpID, Name, Bonus and Address from these tables, the following 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
    2Marry400Brooklyn, New York, USA
    3Jo450Grenelle, Paris, France
    4Kim550Geuzenveld, Amsterdam, Netherlands
    6Huang600Yizhuangzhen, Beijing, China
  • To fetch the above record but with a condition like Bonus should be greater than equal to 500, the following 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
    4Kim550Geuzenveld, Amsterdam, Netherlands
    6Huang600Yizhuangzhen, Beijing, China