Skip to main content

MySQL Self Joins

Introduction​

A self join is a regular join, but the table is joined with itself. Self joins are useful when you want to combine rows with other rows in the same table. This is especially valuable when dealing with hierarchical data or when you need to compare records within the same dataset.

In this tutorial, you'll learn:

  • What self joins are and when to use them
  • How to write self join queries in MySQL
  • Common applications of self joins
  • Best practices and pitfalls to avoid

What is a Self Join?​

A self join is when you join a table to itself. This might sound strange at first, but it's particularly useful when a table contains hierarchical data or when you need to compare rows within the same table.

For a self join to work effectively, you need to:

  1. Use table aliases to distinguish between instances of the same table
  2. Establish a relationship between the table aliases

Syntax of a Self Join​

The basic syntax for a self join is:

sql
SELECT a.column_name, b.column_name
FROM table_name AS a
JOIN table_name AS b
ON a.related_column = b.related_column;

The key points to note here:

  • We use AS a and AS b to create two different aliases for the same table
  • We join these aliases using a common relationship

Self Join Examples​

Example 1: Simple Self Join​

Let's create a simple employees table for our examples:

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'John Smith', NULL),
(2, 'Jane Doe', 1),
(3, 'Mike Johnson', 1),
(4, 'Sarah Williams', 2),
(5, 'David Brown', 2),
(6, 'Emily Davis', 3);

Now, let's use a self join to find each employee along with their manager's name:

sql
SELECT 
    e.employee_id, 
    e.employee_name AS employee, 
    m.employee_name AS manager
FROM 
    employees e
LEFT JOIN 
    employees m ON e.manager_id = m.employee_id;

The result will be:

employee_id | employee       | manager
------------|----------------|-------------
1           | John Smith     | NULL
2           | Jane Doe       | John Smith
3           | Mike Johnson   | John Smith
4           | Sarah Williams | Jane Doe
5           | David Brown    | Jane Doe
6           | Emily Davis    | Mike Johnson

In this query:

  • e represents employees
  • m represents managers (who are also employees in the same table)
  • We use LEFT JOIN to include employees who don't have a manager

Example 2: Finding Employees and Their Direct Reports​

Let's find all managers and the number of employees who report directly to them:

sql
SELECT 
    m.employee_name AS manager,
    COUNT(e.employee_id) AS direct_reports
FROM 
    employees m
LEFT JOIN 
    employees e ON m.employee_id = e.manager_id
GROUP BY 
    m.employee_id, m.employee_name
ORDER BY 
    direct_reports DESC;

Result:

manager        | direct_reports
---------------|---------------
John Smith     | 2
Jane Doe       | 2
Mike Johnson   | 1
Sarah Williams | 0
David Brown    | 0
Emily Davis    | 0

Example 3: Finding Hierarchical Relationships​

Here's how to display the complete organizational hierarchy using self joins:

sql
SELECT 
    CONCAT(IFNULL(m2.employee_name, ''), 
           IF(m2.employee_name IS NOT NULL, ' > ', ''),
           IFNULL(m1.employee_name, ''), 
           IF(m1.employee_name IS NOT NULL, ' > ', ''),
           e.employee_name) AS org_hierarchy
FROM 
    employees e
LEFT JOIN 
    employees m1 ON e.manager_id = m1.employee_id
LEFT JOIN 
    employees m2 ON m1.manager_id = m2.employee_id
ORDER BY 
    org_hierarchy;

Result:

org_hierarchy
---------------------------
John Smith
John Smith > Jane Doe
John Smith > Jane Doe > David Brown
John Smith > Jane Doe > Sarah Williams
John Smith > Mike Johnson
John Smith > Mike Johnson > Emily Davis

Real-World Applications of Self Joins​

1. Organizational Hierarchy​

As demonstrated in our examples, self joins are perfect for representing and querying organizational hierarchies, family trees, and other hierarchical data structures.

2. Flight Connections​

Consider a flights table that contains information about direct flights:

sql
CREATE TABLE flights (
    flight_id INT PRIMARY KEY,
    departure_city VARCHAR(100),
    arrival_city VARCHAR(100),
    duration INT,
    price DECIMAL(10,2)
);

INSERT INTO flights VALUES
(1, 'New York', 'Chicago', 150, 250.00),
(2, 'Chicago', 'Denver', 180, 220.00),
(3, 'New York', 'Denver', 280, 380.00),
(4, 'Chicago', 'Los Angeles', 240, 290.00),
(5, 'Denver', 'Los Angeles', 150, 180.00);

To find all possible connecting flights from New York to Los Angeles with one stopover:

sql
SELECT 
    f1.departure_city AS start,
    f1.arrival_city AS stopover,
    f2.arrival_city AS destination,
    f1.duration + f2.duration AS total_duration,
    f1.price + f2.price AS total_price
FROM 
    flights f1
JOIN 
    flights f2 ON f1.arrival_city = f2.departure_city
WHERE 
    f1.departure_city = 'New York' 
    AND f2.arrival_city = 'Los Angeles';

Result:

start     | stopover | destination  | total_duration | total_price
----------|----------|--------------|----------------|------------
New York  | Chicago  | Los Angeles  | 390            | 540.00
New York  | Denver   | Los Angeles  | 430            | 560.00

3. Product Comparison​

For an e-commerce site, you might want to find products in the same category with price differences:

sql
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Basic Laptop', 'Computers', 699.99),
(2, 'Premium Laptop', 'Computers', 1299.99),
(3, 'Gaming Laptop', 'Computers', 1599.99),
(4, 'Smartphone X', 'Phones', 899.99),
(5, 'Smartphone Y', 'Phones', 799.99);

To find all pairs of products in the same category and their price differences:

sql
SELECT 
    p1.product_name AS product1,
    p2.product_name AS product2,
    p1.category,
    ROUND(ABS(p1.price - p2.price), 2) AS price_difference
FROM 
    products p1
JOIN 
    products p2 ON p1.category = p2.category AND p1.product_id < p2.product_id
ORDER BY 
    p1.category, price_difference;

Result:

product1       | product2       | category  | price_difference
---------------|----------------|-----------|------------------
Basic Laptop   | Premium Laptop | Computers | 600.00
Premium Laptop | Gaming Laptop  | Computers | 300.00
Basic Laptop   | Gaming Laptop  | Computers | 900.00
Smartphone Y   | Smartphone X   | Phones    | 100.00

Note: We use p1.product_id < p2.product_id to avoid duplicate combinations and self-comparisons.

Best Practices for Self Joins​

  1. Always use meaningful aliases: Instead of a and b, use names that indicate the role of each instance, like employee and manager.

  2. Use appropriate join types:

    • INNER JOIN when you only want matches on both sides
    • LEFT JOIN when you want to include records without matches
    • RIGHT JOIN rarely needed in self joins

  3. Careful with recursive hierarchies: For deeply nested hierarchies, consider using recursive CTEs (Common Table Expressions) if your MySQL version supports them (MySQL 8.0+).

  4. Index the joining columns: Ensure that columns used in the join condition are properly indexed for better performance.

  5. Avoid unnecessary columns: Select only the columns you need to improve query performance.

Common Pitfalls to Avoid​

  1. Forgetting aliases: Without distinct aliases, MySQL cannot distinguish between the two instances of the table.

  2. Cartesian products: If you omit the JOIN condition, you'll get a cross join between the table and itself, resulting in n² rows.

  3. Incorrect join conditions: Make sure your join logic correctly captures the relationship you're trying to model.

  4. Performance issues with large tables: Self joins on large tables can be very resource-intensive, so ensure your queries are optimized.

MySQL 8.0+: Using Recursive CTEs for Hierarchies​

If you're using MySQL 8.0 or later, you can also use recursive Common Table Expressions (CTEs) for hierarchical data:

sql
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: employees with no manager (top level)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        0 AS level, 
        employee_name AS hierarchy_path
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        eh.level + 1, 
        CONCAT(eh.hierarchy_path, ' > ', e.employee_name)
    FROM 
        employees e
    JOIN 
        employee_hierarchy eh ON e.manager_id = eh.employee_id
)

SELECT 
    employee_id,
    employee_name,
    level,
    hierarchy_path
FROM 
    employee_hierarchy
ORDER BY 
    hierarchy_path;

Result:

employee_id | employee_name   | level | hierarchy_path
------------|----------------|-------|------------------------
1           | John Smith     | 0     | John Smith
2           | Jane Doe       | 1     | John Smith > Jane Doe
4           | Sarah Williams | 2     | John Smith > Jane Doe > Sarah Williams
5           | David Brown    | 2     | John Smith > Jane Doe > David Brown
3           | Mike Johnson   | 1     | John Smith > Mike Johnson
6           | Emily Davis    | 2     | John Smith > Mike Johnson > Emily Davis

This recursive CTE approach can be more elegant for deep hierarchies than multiple self joins.

Summary​

Self joins are powerful tools in MySQL that allow you to relate records within the same table. They're particularly useful for:

  • Navigating hierarchical relationships
  • Finding connections between related items
  • Comparing records within the same dataset

The key to effective self joins is using clear aliases and establishing the right relationships between the table instances. With practice, self joins will become a valuable part of your SQL toolkit.

Exercises​

To reinforce your understanding of self joins, try these exercises:

  1. Create a cities table with columns city_id, city_name, and distance. Add entries for 5-6 cities along with the distance between some city pairs. Write a self join query to find all possible routes between cities with the total distance.

  2. Create a products table with product_id, product_name, and substitute_product_id (pointing to another product in the same table). Write a query to display each product along with its substitute product's name.

  3. Modify the organizational hierarchy example to display the total number of employees under each manager (direct and indirect reports).

Additional Resources​

Happy coding!


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)