A very common question that often arises in developer interviews is about joins in SQL. Whether you are a SQL developer, .NET developer, Java developer, or Python developer, this question is always popular. One specific type of join that is frequently asked about is the self join.

What is a Self Join?

A self join is simply a normal SQL join that joins one table to itself. It is used when you want to compare values in a column to other values in the same column. This can be useful in various scenarios.

Is Self Join an Inner Join or Outer Join?

A self join can be an inner join, outer join, or even a cross join. It depends on the specific requirements of your query. The decision to use an inner join or outer join in a self join depends on the data and the desired result.

Practical Use of Self Join in the Real World

One practical example of using a self join in the real world is when you have a table with employee data, where each row contains information about an employee and their manager. By using a self join, you can retrieve relevant information about employees and their managers.

Let’s consider an example:

USE TempDb
GO

-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO

-- Insert Sample Data
INSERT INTO Employee
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
GO

-- Check the data
SELECT *
FROM Employee
GO

In the above example, we have created a table called “Employee” with columns for EmployeeID, Name, and ManagerID. The ManagerID column contains the ID of the manager, who is also an employee in the same company.

Now, let’s see how a self join works in this scenario:

-- Inner Join
SELECT e1.Name EmployeeName, e2.Name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

In the result set, we can see all the employees who have a manager. However, this solution has a limitation – it does not display the top manager of the company in the result set. Inner join does not include any results that do not have a manager ID in our scenario.

To overcome this limitation, we can convert the inner join to an outer join:

-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.Name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

By converting the inner join to an outer join, we can now see the top manager in the result set.

In conclusion, a self join can be either an inner join or an outer join, depending on the specific requirements of your query. It is a useful technique when you need to compare values within the same column of a table. Understanding how to use self joins can greatly enhance your SQL skills and enable you to solve complex data retrieval problems.