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
- CREATE TABLE Table1
- (
- ID INT, Name VARCHAR(20)
- )
Table 2
- CREATE TABLE Table2
- (
- ID INT, Name VARCHAR(30)
- )
Table 3
- CREATE TABLE Table3
- (
- ID INT, Name VARCHAR(40)
- )
Now enter some data for Table1, Table2 and Table3. The tables look as shown below:
Table 1
Table 2
Table 3
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.
- 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 Three Tables
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.
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.
- 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