SQL self-join simply is a normal join which is used to join a table to itself. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together. SQL self-join can be any form of join such as inner join, outer join… so you can apply any join to the SQL self-join.
Here is common syntax of SQL self-join.
SELECT column_list
FROM table_A A
INNER JOIN table_A B ON A.column_name1 = B.column_name2….
WHERE row conditions
SQL self-join is very useful when you want to retrieve related data storing in one table such as organizational structure. In our database sample, we have employees table which stores not only employee data but also organization structure. The column reportsTo specifies the manager of an employee and is referenced to employeeId column. To list all information of employees and managers we can use SQL self-join as follows.
SELECT concat(e.firstname, e.lastname) employee,
concat(m.firstname,m.lastname) manager
FROM employees e
INNER JOIN employees m ON m.employeeId = e.reportsTo
Here is the output:
employee manager
-------- -------
Nancy Andrew
Janet Andrew
Margaret Andrew
Steven Andrew
Michael Steven
Robert Steven
Laura Andrew
Anne Steven