Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:
Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.
Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.
SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.
Here are the types of available joins in SQL-Server 2005:
Note: AdventureWorks database is going to be used throughout in this chapter.
Inner Join: It’s a very common and frequently needed join. Common intersection will be pulled out in this kind of join. It means, it will compare the result sets of first table with result sets of second table based on common fields or based on primary key and foreign key relationship. Below given is an example of Inner Join.
SELECT Sales.SalesPerson.SalesPersonID,
Sales.SalesPersonQuotaHistory.SalesQuota
FROM Sales.SalesPerson
INNER JOIN
Sales.SalesPersonQuotaHistory
ON
Sales.SalesPerson.SalesPersonID=Sales.SalesPersonQuotaHistory.SalesPersonID
You can get join two table with SalesPersonID in above case as both have common field.
(Part of Outer Join) Left Outer Join: Include all rows from the left side table no matter whether matching rows are exists in right side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Left Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Left out join has been used so it will list all employees, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Right Outer Join: Include all rows from the right side table no matter whether matching rows are exists in left side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Right Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Right outer join has been used so it will list all employees’ Address, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Full Outer Join: Includes all rows from both tables regardless of match exists. It means it a union distinct of Left Outer Join and Right Outer Join.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Full Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Self Join: A self-join refers back to itself. You can use this type of unary relationship for recursive purpose. Like employee table to find boss.
–creating table for self join
create table emp
(
ID int Identity(1,1),
Name Varchar(15),
BossID int
)
GO
–Inserting some records in above table.
Insert Into emp
SELECT ‘DM’,0 UNION ALL
SELECT ‘MA’,1 UNION ALL
SELECT ‘UC’,2 UNION ALL
SELECT ‘LU’,2
GO
–equi join which will find boss name for every employee, DM is a main boss in this case.
SELECT e.ID,e.Name,ep.Name as ‘boss name’
FROM emp e JOIN emp ep
on
e.bossid=ep.id
GO
There are few more types of join like theta join, equi join, natural join etc. which I will explain in coming article.
Reference: Ritesh Shah