SQL Self Join

A SELF JOIN is a regular join, but the table is joined with itself. This is useful when you need to compare rows within the same table.

Syntax

The basic syntax for a SELF JOIN is:

SELECT a.columns, b.columns
FROM table a
INNER JOIN table b
ON a.column = b.column;

        

Example

Consider a table Employees with columns EmployeeID and ManagerID. To find all employees and their managers, you can use a SELF JOIN:

SELECT e1.EmployeeID AS Employee, e2.EmployeeID AS Manager
FROM Employees e1
INNER JOIN Employees e2
ON e1.ManagerID = e2.EmployeeID;

        

Explanation

• SELF JOIN: Joins a table with itself to compare rows within the same table.
• Aliases: Use table aliases (e.g., e1 and e2) to differentiate between the two instances of the same table.
• ON Clause: Defines the condition for the join, often involving the same column or related columns.