What is a Self-Join and when is it used?

A Self-Join is a type of join in SQL where a table is joined with itself. It is useful when you need to compare rows within the same table or handle hierarchical data.

When to use a Self-Join:

• Hierarchical structures For example, in an employee table where each employee may have a manager who is also an employee in the same table.
• Comparing records To find duplicates or compare values between different rows in the same table.

Example

Consider the following employee data:

To get a list of employees and their managers:

MySQL 8.1
SELECT e.name AS Employee, m.name AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.managerId = m.employeeId;

In this query, we join the Employee table with itself to match each employee with their manager.