MySQL Self Join

Last Updated: 29 June, 2023

A self-join in MySQL is a technique used when we need to join a table with itself. It allows us to combine rows from the same table based on a specific condition. Self-joins can be useful when we have hierarchical or recursive data structures within a single table.

To perform a self-join in MySQL, we need to use table aliases to differentiate between the two instances of the same table.

Here's an example to illustrate the concept:

Let's assume we have a table called "employees" with the following structure:

  +----+---------+-----------+
  | id | name    | manager_id|
  +----+---------+-----------+
  | 1  | John    | NULL      |
  | 2  | Mary    | 1         |
  | 3  | Michael | 1         |
  | 4  | Emma    | 2         |
  +----+---------+-----------+

In this table, each row represents an employee, and the "manager_id" column refers to the "id" of the employee's manager.

Now, let's say we want to retrieve the name of each employee along with the name of their manager. We can achieve this using a self-join query:

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

This query creates two aliases for the "employees" table: "e" for the employee and "m" for the manager. By joining the table on the condition e.manager_id = m.id, we can match each employee with their corresponding manager.

The result of the query will be:

  +---------------+---------------+
  | employee_name | manager_name  |
  +---------------+---------------+
  | John          | NULL          |
  | Mary          | John          |
  | Michael       | John          |
  | Emma          | Mary          |
  +---------------+---------------+

As we can see, the query returns the names of employees along with the names of their respective managers.

Remember that self-joins can be more complex depending on your specific requirements, but this basic example demonstrates the concept.