SQL Self Join

A Self Join is a regular join where a table is joined with itself. It is particularly useful when comparing rows within the same table, such as retrieving employee-manager relationships from an employee table.

Note: A self join is not a new join type. It simply means joining the same table twice with aliases.

Syntax:

SELECT columns
FROM table AS alias1
JOIN table AS alias2 
ON alias1.column = alias2.related_column;

• columns: Columns to retrieve in the result.
• alias1: First reference (alias) of the table.
• alias2: Second reference (alias) of the same table.
• related_column: condition that links rows within same table (e.g., Employee.ManagerID = Manager.EmployeeID).

Example: Employees and Their Managers

We have a table GFGemployees with employee_id, employee_name and manager_id. Each employee is linked to their manager using manager_id. Our goal is to extract employees along with their respective managers’ names.

Here is the query to create the table and insert sample data:

CREATE TABLE GFGemployees (
    employee_id INT PRIMARY KEY, 
    employee_name VARCHAR(50), 
    manager_id INT );

INSERT INTO GFGemployees (employee_id, employee_name, manager_id)
VALUES  (1, 'Zaid', 3),  
               (2, 'Rahul', 3),  
               (3, 'Raman', 4),  
               (4, 'Kamran', NULL),  
               (5, 'Farhan', 4);

Output

To retrieve the list of employees with their corresponding managers, we perform a self join on the GFGemployees table. We use two aliases: e for employees and m for managers.

By joining manager_id from the employee side with employee_id from the manager side, we establish the relationship between employees and their managers.

Query:

SELECT e.employee_name AS employee, m.employee_name AS manager
FROM GFGemployees AS e 
JOIN GFGemployees AS m ON e.manager_id = m.employee_id;

Output

Employees with a valid manager_id are shown. Kamran (who has no manager, NULL) is not listed as an employee, but appears as a manager.

Applications of SQL Self Join

SQL Self Join is widely used in different scenarios, such as:

• Hierarchical Data: Representing organizational structures like employee–manager, category–subcategory or parent–child relationships.
• Finding Relationships: Identifying relationships within the same table, for example linking friends in a social network or mapping task dependencies.
• Data Comparison: Comparing rows within same table, such as salaries of employees in same department.
• Detecting Duplicates: Finding duplicate records in a table by joining it with itself on duplicate criteria.
• Sequential Data Analysis: Comparing current rows with previous or next rows, useful for analyzing trends in sales or time-series data.