Friday, September 20

Self Join-SQL

A table can be joined to itself in a self-join. Use a self-join when you want to create a result 
set that joins records in a table with other records in the same table. To list a table two times 
in the same query, you must provide a table alias for at least one of instance of the table 

For explaining this concept we will use AdventureWorksDW2008R2.
Table used is DimEmployee, we will select few columns needed for

     Select EmployeeKey, FirstName, LastName, ParentEmployeeKey, FirstName, LastName from DimEmployee "


The employees called Rob Walters, Gail Erickson, Jossef Goldberg has a primary key of
4,511,13 and a foreign key of 3. This means he works for the employee with a primary key 
of 3. That person is Roberto Tamburello, who in turn reports to Terri Duffy-14.

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName, M.LastName from DimEmployee as E join DimEmployee as M
on E.ParentEmployeeKey = M.EmployeeKey "

For understanding well write query as shown below, here we use inner join.

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName as Manager_FirstName,M.LastName as Manager_LastName 
from DimEmployee as E join DimEmployee as 
on E.ParentEmployeeKey = M.EmployeeKey "

The output will display 295 rows out of 296, i.e. one employee does not have a manager.
In order to display all the 296 records, i.e. employee without manager also; we can use Left 
outer join instead of Inner Join.
so the query can be re-written as:

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName as Manager_FirstName, M.LastName as Manager_LastName
from DimEmployee as E  left outer join  DimEmployee as 
on E.ParentEmployeeKey = M.EmployeeKey "