SQL Self Join

SQL Self Join is used when a table needs to be joined with itself to compare rows within the same table. It helps in finding relationships between records in a single table by treating it as two separate instances using table 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

Let's create 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.

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:

• A self join is performed on the same table to match employees with their managers.
• Two aliases are used: e for employees and m for managers.
• The employee’s manager_id is matched with the manager’s employee_id.
• This helps display each employee along with their manager’s name.

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.

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