In Focus

Joining Three or More Tables in SQL Server 2012

This article will give you an idea of how to join three or more tables and also defines the join in SQL Server.

This article will give you an idea of how to join three or more tables and also defines the join in SQL Server. If You want to retrieve data from multiple tables then you need to use joins in SQL Server. Joins are used to get data from two or more tables based on the relationships among some of the columns in the tables. Here, I have constructed a join query that provides a solution for joining three tables into one table. Let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Creating Table in SQL Server


Now to create 3 tables in the Master database named Table1, Table2 and Table3.
 
Table 1
  1. CREATE TABLE Table1  
  2. (  
  3. ID INTName VARCHAR(20)  
  4. )  
Table 2
  1. CREATE TABLE Table2  
  2. (  
  3. ID INTName VARCHAR(30)  
  4. )  
Table 3
  1. CREATE TABLE Table3  
  2. (  
  3. ID INTName VARCHAR(40)  
  4. )  
Now enter some data for Table1, Table2 and Table3. The tables look as shown below:
 
Table 1
 
Table1-in-SQL-Server.jpg 
 
Table 2
 
Table2-in-SQL-Server.jpg 
 
Table 3
 
Table3-in-SQL-Server.jpg 
 

Inner Join in SQL Server

 
Inner joins are used to return those and only those rows from both joined tables which satisfy the join condition. The join condition is the equality of two columns, for example one from the table country and another from the table address. An INNER JOIN is the most common join type. An inner join simply looks for rows matching in both tables.
 
Syntax
 
The Inner join syntax is as follows:
 
SELECT <column list>
FROM <left joined table>
[INNER] JOIN <right joined table>
ON <join condition>
 
The keyword INNER sometimes is avoided, but anyway it is an inner join. After the keyword ON, the join condition is provided.
 

Inner Join with Two Tables

 
In this example, we use the above two tables, table1 and table2, and adding it using an Inner Join.
  1. Select table1.ID ,table1.Name from Table1 inner join Table2 on Table1.ID =Table2 .ID  
Output
 
The output will be displayed as a single table which follows the join condition "Table1.ID = Table2.ID".
 
Inner-join-with-two-tables-in-SQL-Server.jpg
 

Inner Join with Three Tables

 
In this example we use all three of the preceding tables; table1, Table2 and table3 and adding it using an Inner Join.
  1. Select table1.ID ,table1.Name 
  2. from Table1 inner join Table2 on Table1 .ID =Table2 .ID  
  3. inner join Table3 on table2.ID=Table3 .ID  
Output
 
The output will be displayed as a single table which satisfies the join conditions.
 
Inner-join-with-three-tables-in-SQL-Server.jpg
 
Where Condition (Inner Join with Three Tables)
 
In this example we use all three of the preceding tables; table1, Table2 and table3 and adding it using an Inner Join with a where condition.
  1. Select table1.ID ,table1.Name 
  2. from Table1 inner join Table2 on Table1 .ID =Table2 .ID inner join Table3 on table2.ID=Table3 .ID  
  3. where table1.Name=Table3.Name  
Output
 
Inner-join-with-three-tables-and-where-in-SQL-Server.jpg