MySQL - Self Join Queries

In MySQL, a self-join is a type of join operation where a table is joined with itself. This means that we can use a table as if it were two separate tables, allowing us to compare data within the same table. Self-joins can be useful in situations where we need to compare data within a table, or when we want to create a hierarchy or relationship between rows within the same table.

Here are a few examples of self-joins in MySQL, along with the corresponding table structures:

Example 1: Employee Hierarchy

Consider a table that stores employee information, including their ID, name, and manager ID (which is also an employee ID). We can use a self-join to create a hierarchy of employees, where each employee's manager is also an employee in the same table.

Table structure:

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(255),

    manager_id INT

);

To create a hierarchy of employees, we can use a self-join like this:

SELECT e1.name AS employee_name, e2.name AS manager_name

FROM employees e1

JOIN employees e2 ON e1.manager_id = e2.id;

This query joins the employees table with itself, using the manager ID of each employee to find their manager's ID in the same table. The result is a list of employees and their respective managers.

Example 2: Product Relationships

Consider a table that stores product information, including their ID, name, and parent ID (which is also a product ID). We can use a self-join to create a relationship between products, where each product's parent is also a product in the same table.

Table structure:

CREATE TABLE products (

    id INT PRIMARY KEY,

    name VARCHAR(255),

    parent_id INT

);

To create a relationship between products, we can use a self-join like this:

SELECT p1.name AS product_name, p2.name AS parent_name

FROM products p1

JOIN products p2 ON p1.parent_id = p2.id;

This query joins the products table with itself, using the parent ID of each product to find its parent's ID in the same table. The result is a list of products and their respective parent products.

Example 3: Employee Peer Comparison

Consider a table that stores employee information, including their ID, name, and department. We can use a self-join to compare employees within the same department, to see how their salaries compare.

Table structure:

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(255),

    department VARCHAR(255),

    salary INT

);

To compare employees within the same department, we can use a self-join like this:

SELECT e1.name AS employee_name, e2.name AS peer_name, e1.salary, e2.salary

FROM employees e1

JOIN employees e2 ON e1.department = e2.department

WHERE e1.id <> e2.id;

This query joins the employees table with itself, using the department of each employee to find other employees in the same department. The WHERE clause ensures that we don't compare an employee to themselves. The result is a list of employees and their respective peers within the same department, along with their respective salaries.