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,
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.
To solve this, we need to do a Self Join on the
Employees table. We would join them based on the
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.