SELF JOIN

A Self Join in SQL is a join operation where a table is joined with itself. In other words, each row of the table is combined with itself and with every other row of the table.

In a self join, you cannot simply mention the same table name twice because SQL does not know how to differentiate between them. To perform a self join, you need to alias (give another name to) the table to avoid confusion. Here is a diagram representing SELF JOIN:

The syntax for a Self Join is similar to the syntax for other joins, but you will use the same table and differentiate them with aliases.

SELECT column_name(s)
FROM table1 AS alias1
JOIN table1 AS alias2
ON alias1.column_name = alias2.column_name;

Let’s consider a single table, Employees:

Employees

EmployeeID  | EmployeeName  | ManagerID
----------- | ------------- | ----------
1           | John          | NULL
2           | Maria         | 1
3           | Steve         | 1
4           | Michael       | 2
5           | Jenny         | 2
6           | Brad          | 3

Example Test Question

Prepare a list that shows each employee along with their manager’s name.

Answer

To solve this, we need to do a Self Join on the Employees table. We would join them based on the EmployeeID and ManagerID.

Here is the SQL query to achieve this:

SELECT E1.EmployeeName AS 'Employee', E2.EmployeeName AS 'Manager'
FROM Employees AS E1
INNER JOIN Employees AS E2
ON E1.ManagerID = E2.EmployeeID;

This would return the following result:

Employee  | Manager
--------- | -------
Maria     | John
Steve     | John
Michael   | Maria
Jenny     | Maria
Brad      | Steve

In the result, you can see that each employee is listed with the corresponding manager. The self join allowed us to refer to the same table as if it were two different tables in the query, enabling us to match the employees with their managers.

This is how a Self Join works in SQL, providing a result set that includes rows of a single table joined with other rows of the same table.