In this Article I m going to show different types of Join.
Introduction
The nature of relational database design shows that we will have related data that is stored in different tables. To retrieve data from two or more tables we use JOINS.
The JOIN clause tells the database how the data in the two tables is related so that it can return a correct representation of the related data.
There are different types of join.
Types of Join are :
1.Self Join
2.Inner Join
3.Outer Join
3.1. Right Outer Join
3.2. Left Outer Join
3.3 Full Outer Join
4.Cross join Department Table
Database Design for Department Table
Data for Department Table
Database Design for Employee Table
Data for Employee Table
Self-Join:
self-Join in Sql server helps in returning the record having some relation or similarity with other record in the same database table.
select e1.EmployeeName 'Manager', e2.EmployeeName 'Employee' from Employee e1 join Employee e2 on
e1.EmployeeID =e2.ManagerID
Inner Join:
Inner Inner Join is a Default Types of join in the Sql Server.It Uses Logical Operators such as '<','>','<>' to match the c=each record in two tables.
Department Table
Employee Table
select e.EmployeeID,e.EmployeeName,e.Salary,d.DepartmentID,d.DepartmentName from Employee e Inner Join Department d on e.EmployeeID=d.EmployeeID where e.Salary>50000
Outer Join :
A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You can create three different outer JOINs to specify the unmatched rows to be included:
1 Right outer Join
2. Left Outer Join
3. Full Outer Join
1 Right Outer Join
Right Outer Join:
In right outer join returns all the rows from the table specified right side of the join clause. It returns Null value if no match in the Left Join Table.
Department Table
Employee Table
Select e.EmployeeID,e.EmployeeName,e.City,d.DepartmentName from Employee e Right Outer Join Department d on e.EmployeeID=d.EmployeeID order by e.EmployeeName
In this Table City which is in the Left table Returns Null Value.
Left Outer Join :
In left outer join returns all the rows from the table specified Left side of the join clause. It returns Null value if no match in the Right Join Table.
Department Table
EmployeeTable
Select e.EmployeeID,e.EmployeeName,e.City,d.DepartmentName from Employee e Left Outer Join Department d on e.EmployeeID=d.EmployeeID order by e.EmployeeName
Full Outer Join:
Full Outer Join returns all the rows from both the Left and Right table. If there is any matches missing from the Left table then it returns Null value for the Left side Table. .If there is any matches missing from the Right table then it returns Null value for the Right side Table.
Department Table
Employee Table
Select e.EmployeeID,e.EmployeeName,e.City,d.DepartmentName from Employee e Full Outer Join Department d on e.EmployeeID=d.EmployeeID order by e.EmployeeName
Cross Join:
Cross join works on a Cartesian products of rows for both Left and Right table.It combines rows of the Left table with all the rows of Right Table.
select EmployeeName,Salary,DepartmentName from Employee cross join Department order by EmployeeName
It returns 10*10=100 results by multiplying each row of auther Employee with Department table.