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.

• Returning all rows from the same table.
• Used to compare different records from same table.
• Used to find duplicate records.

SYNTAX

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

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