SELF JOIN in MySQL

A SELF JOIN is a type of JOIN where a table is joined with itself. It is used when rows in a table need to be compared with other rows in the same table.

1. Syntax

SELECT A.column1, B.column2FROM table_name AJOIN table_name B ON A.common_column = B.common_column;

• The same table is used twice, with different aliases (A and B).
• It allows comparing records within the same table.

2. Example Table: employees

🔹 Each employee has a manager_id that refers to another emp_id in the same table.

3. Using SELF JOIN to Find Employee-Manager Relationships

SELECT A.name AS Employee, B.name AS ManagerFROM employees AJOIN employees B ON A.manager_id = B.emp_id;

✅ Result:

Explanation

• The employees table is used twice:
  • A represents employees.
  • B represents their managers.
• The JOIN is done using A.manager_id = B.emp_id, linking each employee to their manager.

4. Finding Employees Without a Manager (LEFT JOIN)

SELECT A.name AS Employee, COALESCE(B.name, 'No Manager') AS ManagerFROM employees ALEFT JOIN employees B ON A.manager_id = B.emp_id;

✅ Result:

🔹 COALESCE(B.name, 'No Manager') replaces NULL with "No Manager".

Key Takeaways

✅ SELF JOIN is used to compare records within the same table.
✅ It is useful for hierarchical data (e.g., employees & managers).
✅ Aliases (A, B) are used to differentiate between the two instances of the same table.
✅ LEFT JOIN helps find records with no match (e.g., employees without managers).