T-SQL - INNER JOIN

The T-SQL (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.

Syntax

The syntax for using INNER JOIN keyword in T-SQL (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

Table 2: Contact_Info table

• 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:

  Name	Age	Address
  Marry	24	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Huang	28	Yizhuangzhen, 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:

  EmpID	Name	City	Age	Salary	Address
  2	Marry	New York	24	2750	Brooklyn, New York, USA
  3	Jo	Paris	27	2800	Grenelle, Paris, France
  4	Kim	Amsterdam	30	3100	Geuzenveld, Amsterdam, Netherlands
  6	Huang	Beijing	28	2800	Yizhuangzhen, 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 T-SQL (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

Table 2: Bonus_Paid table

Table 3: Contact_Info table

• 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:

  EmpID	Name	Bonus	Address
  2	Marry	400	Brooklyn, New York, USA
  3	Jo	450	Grenelle, Paris, France
  4	Kim	550	Geuzenveld, Amsterdam, Netherlands
  6	Huang	600	Yizhuangzhen, 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:

  EmpID	Name	Bonus	Address
  4	Kim	550	Geuzenveld, Amsterdam, Netherlands
  6	Huang	600	Yizhuangzhen, Beijing, China