Different Types of Join in SQL server

Anweshabhowmick
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 46793 red flag
Rating: 5 out of 5  
 2 vote(s)

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. 

Page copy protected against web site content infringement by Copyscape

About the Author

Anweshabhowmick
Full Name: Anwesha Bhowmick
Member Level: Starter
Member Status: Member
Member Since: 9/23/2010 2:57:02 AM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Prabhakar on: 2/26/2011 | Points: 25
Gud Article . .
Posted by: Madhu.b.rokkam on: 2/26/2011 | Points: 25
Nice article..
Posted by: Akiii on: 2/26/2011 | Points: 25
Very descriptive.....

Thank you very much
Akiii
Posted by: Akiii on: 2/26/2011 | Points: 25
Hi Anwesha Bhowmick,

I have come across of more joins and they are:-
(1)SQL Inner Natural Join
(2)SQL Inner Equi Join
(3)non equi join
(4)Natural join

Can you please explain what are the above types. I think natural join is just the cartesian product of two tables.

Thanks and Regards
Akiii

Posted by: Anweshabhowmick on: 2/26/2011 | Points: 25
Thanks Prabhakar and Madhu

Login to post response

Comment using Facebook(Author doesn't get notification)