self join in sql server?

 »

SQL SERVER – 2005 Explanation and Example – SELF JOIN

June 3, 2007 by pinaldave

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.

--SELF JOIN

CREATE TABLE Employee246(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)

INSERT INTO Employee246
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2

select * from Employee246

select e2.Name,e1.Name as manager from Employee246 e1
inner join
Employee246 e2
on
e2.EmployeeID=e1.ManagerID