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