The Different Types of Joins in SQL Server
Inner join or Equi join
Outer Join
Cross join
Let's suppose we have two tables Emp and Dept whose description is given below:-
CREATE TABLE [Emp](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Deptid] [INT]
)
CREATE TABLE [Dept](
[Deptid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DeptName] [nvarchar](255) NOT NULL
)
After the creation of the tables we need to insert the data into these tables. To insert the data the following queries are used:-
insert into Emp (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Deptid)
values('E1','Sachin','Tendulkar','Sachin@mycomp.com',2,2)
insert into Emp (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Deptid)
values('E2','Zaheer','Khan','Zaheer@mycomp.com',1,1)
insert into Emp(EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Deptid)
values('E3','Gambhir','Gautam','Gambhir@mycomp.com',1,2)
insert into Emp (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Deptid)
values('E4','Dhoni','MS','Dhoni@mycomp.com',1,NULL)
insert into Dept(DeptName)
values('Bowling')
insert into Dept(DeptName)
values('Batting')
insert into Dept(DeptName)
values('Coach')
insert into Dept(DeptName)
values('Allrounder')
Inner Join
This type of join is also known as the Equi join.
This join returns all the rows from both tables where there is a match.
This type of join can be used in the situation where we need to select only those rows
which have values common in the columns which are specified in the ON clause.
Now, if we want to get Emp id, Emp first name and their Dept name
for those Emps which belongs to at least one Dept, then we can use the inner join.
Query for Inner Join
SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DeptName
FROM Emp
INNER JOIN dept
ON Emp.Deptid=Dept.Deptid
Result
Empid EmpFirstName DeptName
1 Sachin Batting
2 Zaheer Bowling
3 Gambhir Batting
Explanation
In this query, we used the inner join based on the column "Deptid" which is common in both the tables "Emp" and "Dept".
This query will give all the rows from both the tables which have common values in the column "Deptid".
Gambhir Guatam and Sachin Tendulkar has the value "2" in the Deptid column of the table Emp.
In the Dept table, the Dept "Batting" has the value "2" in the Deptid column.
Therefore the above query returns two rows for the Dept "Batting", one for Gambhir Guatam and another for Sachin Tendulkar.
Lets understand more joins
