Joins are used to get data from two or more tables. We can combine the tables data with the common fields between tables.
There are four types of Joins in SQL Server
- Inner Join
- Outer Join
- Left outer join (Left Join)
- Right outer join (Right Join)
- Full Outer Join (Full Join)
- Cross Join
- Self Join
Inner Join – It is used to get data from two or more tables when common column values matches between tables. If no column values matches between tables then no data will be return.
Example: Lets create an Employee table and Department table.
CREATE TABLE #Employee
(
EmpId int,
EmpName Varchar(100),
DepartmentId int,
ManagerId int
)
Insert into #Employee(EmpId,EmpName,DepartmentId,ManagerId)
Select 1,'Hitesh',4,Null Union
Select 2,'Priyanka',1,Null Union
Select 3,'Rahul',Null,Null Union
Select 4,'Dev',4,1 Union
Select 5,'Neha',4,1 Union
Select 6,'Vikas',Null,1 Union
Select 7,'Abhimanyu',3,Null
CREATE TABLE #Department
(
DepartmentId int,
DepartName Varchar(100)
)
Insert into #Department(DepartmentId,DepartName)
Select 1,'English' Union
Select 2,'Java' Union
Select 3,'PHP' Union
Select 4,'Dot Net' Union
Select 5,'SQL' Union
Select 6,'Physics'
Inner Join Query
Select E.EmpName,D.DepartName
From #Employee E(Nolock) inner join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Inner Join
Outer Join – There are three types of Outer Join in SQL Server.
Left outer join (Left Join) – Left outer join is also known as Left Join. It is used to get data from two or more tables. It will return all the rows from left table and matching rows from right table.
Select E.EmpName,D.DepartName
From #Employee E(Nolock) left join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Left Join
Right outer join (Right Join) – Right outer join is also known as Right Join. It is used to get data from two or more tables. It will return all the rows from right table and matching rows from left table.
Select E.EmpName,D.DepartName
From #Employee E(Nolock) right join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Right Join
Full Outer Join (Full Join) – Full outer join is also known as Full Join. It is used to get data from two or more tables. it returns all the rows from left table and right table. It combines the result of both left and right joins.
Select E.EmpName,D.DepartName
From #Employee E(Nolock) FULL OUTER JOIN #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Full Outer Join
Cross Join – It is used to get data from two or more tables. It will return Cartesian product of two tables.
Select E.EmpName,D.DepartName
From #Employee E(Nolock) cross join #Department D(Nolock)

Cross Join
Self Join – It is used when a table references data in itself.
Select E.EmpName ,M.EmpName as ManagerName
From #Employee E(Nolock) , #Employee M(Nolock) where E.ManagerId=M.EmpId

Self Join