MySQL Self Join

 MySQL Self Join

Summary: in this tutorial, you will learn how to use MySQL self join which joins a table to itself using the inner join or left join.

In the previous tutorials, you have learned how to join a table to the other tables using  INNER JOIN,  LEFT JOIN, RIGHT JOIN, or CROSS JOIN clause. However, there is a special case in which you need to join a table to itself, which is known as a self-join.

The self-join is often used to query hierarchical data or to compare a row with other rows within the same table.

To perform a self-join, you must use table aliases to not repeat the same table name twice in a single query. Note that referencing a table twice or more in a query without using table aliases will cause an error.

MySQL self-join examples

Let’s take a look at the employees table in the sample database.

The table employees stores not only employees' data but also the organization's structure data. The reportsto column is used to determine the manager id of an employee.

1) MySQL self-join using INNER JOIN clause

To get the whole organization structure, you can join the employees table to itself using the employeeNumber  reportsTo columns. The table employees has two roles: one is the Manager and the other is Direct Reports.

SELECT 
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
    employees e
INNER JOIN employees m ON 
    m.employeeNumber = e.reportsTo
ORDER BY 
    Manager;

The output only shows the employees who have a manager. However, you don’t see the President because his name is filtered out due to the INNER JOIN clause.

2) MySQL self-join using LEFT JOIN clause

The President is the employee who does not have any manager or value in the reportsTo column is NULL .

The following statement uses the LEFT JOIN clause instead of INNER JOIN to include the President:

SELECT 
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
            'Top Manager') AS 'Manager',
    CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
    employees e
LEFT JOIN employees m ON 
    m.employeeNumber = e.reportsto
ORDER BY 
    manager DESC;

3) Using MySQL self-join to compare successive rows

By using the MySQL self-join, you can display a list of customers who are located in the same city by joining the customers table to itself.

SELECT 
    c1.city, 
    c1.customerName, 
    c2.customerName
FROM
    customers c1
INNER JOIN customers c2 ON 
    c1.city = c2.city
    AND c1.customername > c2.customerName
ORDER BY 
    c1.city;

In this example, the table customers is joined to itself using the following join conditions:

• c1.city = c2.city  Make sure that both customers have the same city.
• c.customerName > c2.customerName ensures that no same customer is included.

In this tutorial, you have learned how to the MySQL self join that to join a table to itself using the INNER JOIN or LEFT JOIN clauses.