MySQL - Self Join

MySQL: The Self Join

Table of Content

- What is a Self Join?
- A Classic Example: Employees and Managers
- Self Join with an ORDER BY Clause
- Performing Self Joins in Client Applications

What is a Self Join?

A self join is a regular join, but instead of joining two different tables, you join a table to itself. This is useful when a table contains hierarchical data or records that reference other records within the same table.

To perform a self join, you must use table aliases to give the table two different temporary names within the query. This allows the database to treat it as two separate tables, enabling you to compare columns from different rows of the same table.

Best practice is to use the explicit JOIN ... ON syntax, which is clearer and more standard than the older, implicit comma-based syntax.

Modern Syntax

SELECT
    alias1.column_name,
    alias2.column_name
FROM
    table_name AS alias1
JOIN
    table_name AS alias2 ON alias1.common_field = alias2.related_field;

A Classic Example: Employees and Managers

The most common use case for a self join is querying an employees table where each employee record might have a manager_id that points to another employee’s id in the same table.

First, let’s create and populate our employees table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    manager_id INT NULL, -- Can be NULL for top-level employees (e.g., CEO)
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
) ENGINE=InnoDB;

Now, let’s insert some data. Note that the CEO (Jane Smith) has a NULL manager_id.

INSERT INTO employees (employee_id, first_name, last_name, manager_id) VALUES
(1, 'Jane', 'Smith', NULL),
(2, 'John', 'Doe', 1),
(3, 'Peter', 'Jones', 1),
(4, 'Mary', 'Williams', 2),
(5, 'David', 'Brown', 2);

The table now looks like this:

employee_id	first_name	last_name	manager_id
1	Jane	Smith	NULL
2	John	Doe	1
3	Peter	Jones	1
4	Mary	Williams	2
5	David	Brown	2

Let’s use a self join to list each employee and their manager’s name. We’ll use the aliases e for the employee and m for the manager.

We use a LEFT JOIN here because we want to include the CEO, who has no manager. A regular INNER JOIN would exclude her.

SELECT
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM
    employees AS e
LEFT JOIN
    employees AS m ON e.manager_id = m.employee_id;

Output

The result clearly shows the relationship between employees and their managers:

employee_first_name	employee_last_name	manager_first_name	manager_last_name
Jane	Smith	NULL	NULL
John	Doe	Jane	Smith
Peter	Jones	Jane	Smith
Mary	Williams	John	Doe
David	Brown	John	Doe

Self Join with an ORDER BY Clause

You can add an ORDER BY clause to sort the results, making them easier to read. Let’s sort the previous result by the manager’s name, then by the employee’s name.

SELECT
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    CONCAT(m.first_name, ' ', m.last_name) AS manager_name
FROM
    employees AS e
LEFT JOIN
    employees AS m ON e.manager_id = m.employee_id
ORDER BY
    manager_name ASC, employee_last_name ASC;

Output

The sorted result is more organized:

employee_first_name	employee_last_name	manager_name
Jane	Smith	NULL
John	Doe	Jane Smith
Peter	Jones	Jane Smith
David	Brown	John Doe
Mary	Williams	John Doe

Performing Self Joins in Client Applications

Executing a self join from a client program is the same as any other query. Here are modern examples demonstrating this.

Python
Node.js
PHP
Java



**Setup:** `pip install mysql-connector-python`

```python
import mysql.connector

db_config = { 'host': 'localhost', 'user': 'user', 'password': 'password', 'database': 'your_db' }

query = """
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;
"""

try:
    with mysql.connector.connect(**db_config) as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(query)
            for row in cursor.fetchall():
                print(f"Employee: {row['employee_first_name']} {row['employee_last_name']}, Manager: {row['manager_name'] or 'N/A'}")
except mysql.connector.Error as e:
    print(f"Error: {e}")

Setup: npm install mysql2

const mysql = require('mysql2/promise');

const dbConfig = { host: 'localhost', user: 'user', password: 'password', database: 'your_db' };

const query = `
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;`;

async function getEmployeeHierarchy() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(query);
        rows.forEach(row => {
            console.log(`Employee: ${row.employee_first_name} ${row.employee_last_name}, Manager: ${row.manager_name || 'N/A'}`);
        });
    } catch (error) {
        console.error(`Error: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

getEmployeeHierarchy();

Setup: Use Composer.

<?php
$dbConfig = ['host' => 'localhost', 'user' => 'user', 'password' => 'password', 'database' => 'your_db'];

$query = "
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli($dbConfig['host'], $dbConfig['user'], $dbConfig['password'], $dbConfig['database']);
    $result = $mysqli->query($query);
    while ($row = $result->fetch_assoc()) {
        $manager = $row['manager_name'] ?? 'N/A';
        echo "Employee: {$row['employee_first_name']} {$row['employee_last_name']}, Manager: {$manager}\n";
    }
} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

?>

Setup: Add MySQL JDBC driver to your project.

import java.sql.*;

public class SelfJoinExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "user";
        String password = "password";

        String query = """
            SELECT
                e.first_name AS employee_first_name,
                e.last_name AS employee_last_name,
                CONCAT(m.first_name, ' ', m.last_name) AS manager_name
            FROM
                employees AS e
            LEFT JOIN
                employees AS m ON e.manager_id = m.employee_id
            ORDER BY
                manager_name, employee_last_name;""";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                String managerName = rs.getString("manager_name");
                System.out.printf("Employee: %s %s, Manager: %s%n",
                        rs.getString("employee_first_name"),
                        rs.getString("employee_last_name"),
                        (managerName == null) ? "N/A" : managerName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}