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