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

Table1

CREATE TABLE Table1

(

ID INT, Name VARCHAR(20)

)

Table2

CREATE TABLE Table2

(

ID INT, Name VARCHAR(30)

)

Table3

CREATE TABLE Table3

(

ID INT, Name VARCHAR(40)

)

 

Now enter some data for Table1, Table2 and Table3. The tables look as shown below:

 

Table1

 

Table1-in-SQL-Server.jpg

 

Table2

 

Table2-in-SQL-Server.jpg

 

Table3

 

Table3-in-SQL-Server.jpg

Inner Join

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

Example

In this example we use the above two tables, table1 and table2, and adding it using an Inner Join.

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

Example

In this example we use all three of the preceding tables; table1, Table2 and table3 and adding it using an Inner Join.

Select table1.ID ,table1.Name from Table1 inner join Table2 on Table1 .ID =Table2 .ID

 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)

Example

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.

Select table1.ID ,table1.Name from Table1 inner join Table2 on Table1 .ID =Table2 .ID inner join Table3 on table2.ID=Table3 .ID

where table1.Name=Table3.Name

Output

Inner-join-with-three-tables-and-where-in-SQL-Server.jpg

Next Recommended Readings