SQL SELF JOIN Overview

A SQL SELF JOIN is a join operation where a table is joined with itself. This is useful when you need to compare rows within the same table or to find relationships among rows in a single table.

When to Use SELF JOIN

A SELF JOIN is particularly useful in scenarios such as:

• Finding hierarchical data (e.g., organizational charts).
• Identifying duplicate rows within the same table.
• Comparing rows to identify patterns or relationships.

SELF JOIN Syntax

The syntax for a SELF JOIN is:

SELECT A.column_name, B.column_name
FROM table_name A
JOIN table_name B ON A.column_name = B.column_name
WHERE condition;

SQL SELF JOIN Example

To understand SELF JOIN, let’s use the following unique table:

Employees Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    ManagerID INT
);

INSERT INTO Employees (EmployeeID, Name, ManagerID)
VALUES
(1, 'John Doe', NULL),
(2, 'Jane Smith', 1),
(3, 'Alice Johnson', 1),
(4, 'Bob Brown', 2),
(5, 'Charlie Davis', 2);

Example 1: Finding Employees and Their Managers

Suppose we want to find the names of employees and their managers from the Employees table. Here’s how you can do it with a SELF JOIN:

SELECT 
    e1.Name AS EmployeeName,
    e2.Name AS ManagerName
FROM 
    Employees e1
LEFT JOIN 
    Employees e2
ON 
    e1.ManagerID = e2.EmployeeID;

In this query:

• e1 is an alias for the first instance of the Employees table, representing employees.
• e2 is an alias for the second instance of the Employees table, representing managers.
• The LEFT JOIN ensures that even employees without managers (e.g., top-level managers) are included in the result with NULL for the manager’s name.

Example 2: Finding Employees with the Same Manager

If we want to find employees who share the same manager, we can use the following query:

SELECT 
    e1.Name AS Employee1,
    e2.Name AS Employee2,
    e1.ManagerID
FROM 
    Employees e1
JOIN 
    Employees e2
ON 
    e1.ManagerID = e2.ManagerID
AND 
    e1.EmployeeID <> e2.EmployeeID;

In this query:

• We join the Employees table with itself where ManagerID matches.
• e1.EmployeeID <> e2.EmployeeID ensures that we are not matching an employee with themselves.

Important Points About SQL SELF JOIN

• A SELF JOIN is a regular join where the table is joined with itself using table aliases.
• It helps in scenarios where you need to compare rows within the same table or find relationships among them.
• The SELF JOIN can use any type of join (INNER JOIN, LEFT JOIN, etc.) depending on the requirements.

SQL SELF JOIN with Aliases Example

Using table aliases makes the query more readable:

SELECT 
    emp1.Name AS EmployeeName,
    emp2.Name AS ManagerName
FROM 
    Employees emp1
LEFT JOIN 
    Employees emp2
ON 
    emp1.ManagerID = emp2.EmployeeID;