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

1. Inner Join
2. Outer Join
   • Left outer join (Left Join)
   • Right outer join (Right Join)
   • Full Outer Join (Full Join)
3. Cross Join
4. 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