MySQL : Self Join

In MySQL, a self join is a join operation where a table is joined with itself. This can be useful when you have a table that contains hierarchical data or when you want to compare rows within the same table.

Here's the basic syntax of a self join:

SELECT t1.column1, t2.column2
FROM table_name t1
JOIN table_name t2 ON t1.columnX = t2.columnY;
 

• table_name: The name of the table you want to perform the self join on.
• t1, t2: Aliases for the same table.
• columnX, columnY: Columns used for the join condition.

Example:

Let's consider a table named employees with the following columns: employee_id, employee_name, and manager_id. In this scenario, manager_id is a foreign key that refers to the employee_id of the manager.

Suppose you want to retrieve the names of employees along with the names of their managers. You can achieve this with a self join:

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
 

In this query:

• e and m are aliases for the employees table, representing two instances of the same table.
• The join condition e.manager_id = m.employee_id matches each employee's manager_id with the corresponding manager's employee_id.
• This retrieves the names of employees along with the names of their managers.

Self joins can also be used in more complex scenarios, such as hierarchical data structures like organization charts or product categories. They allow you to compare and relate rows within the same table, making them a powerful tool for data analysis and reporting.