SQL Server - Self Joins

To join tables, at-least two tables are required. It does not necessarily have to be two different tables :). It is possible to join a table with itself to retrieve the required data.

When a table is joined with itself then such type of join is called Self Join. This is useful when you want to retrieve the data in the form of organizational structure. The same table is used twice using aliases in self join.

The methodology involves joining the records in a table with other records in the same table.

Following is an example of a Self Join.

CREATE TABLE #sample1 (EmpID INT, EmpName VARCHAR (30), ManageriD INT)

INSERT INTO #sample1 VALUES (1001, 'Reman D Joseph', Null)
INSERT INTO #sample1 VALUES (1002, 'Samuel Peter', 1001)
INSERT INTO #sample1 VALUES (1003, 'Arihanth', 1005)
INSERT INTO #sample1 VALUES (1004, 'Ayesha Khan', 1001)
INSERT INTO #sample1 VALUES (1005, 'Kirlosker Joseph', 1002)
INSERT INTO #sample1 VALUES (1006, 'Firdouse', 1004)
INSERT INTO #sample1 VALUES (1007, 'Peter DeSouja', 1002)

SELECT * FROM #Sample1

Observe the following by looking into the values:

Samuel Peter's Employee ID is 1002 and his manager's employee ID is 1001. The ID 1001 is Reman D Joseph's employee ID hence Samuel Peter's manager is Reman D Joseph, right?

Let's now check how this information can be more conveniently retrieved using SQL Joins:

SELECT DISTINCT s1.EmpName, s2.EmpName FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD

The above query will return all the employees with a manager along with their names.

What if we want to retrieve a list of all the employees regardless of having a manager or not?

SELECT DISTINCT s1.EmpID, s1.EmpName, s2.EmpName FROM #sample1 s1
LEFT JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD
ORDER BY s1.EmpID

If you want to combine the employee’s name with his manager's name, then use the following query:

SELECT DISTINCT s1.EmpName + '''s Manager is ' + s2.EmpName
FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD

To generate a list of employees who are not managers use the following query (An example for Sub-query and Joins)

SELECT EmpID, EmpName, ManagerID FROM #Sample1
WHERE EmpID NOT IN(
SELECT DISTINCT s1.ManageriD FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD)

To generate a list of Only managers, use the query below:

SELECT EmpID, EmpName, ManagerID FROM #Sample1
WHERE EmpID IN(
SELECT DISTINCT s1.ManageriD FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD)

DROP TABLE #sample1