Self JOIN in MySQL

Introduction

A Self JOIN lets you compare rows in the same table to find related data.

Finding employees and their managers in the same employee table.

Comparing products with similar features in one product list.

Finding pairs of students who share the same class.

Matching parts that are related in a parts inventory.

Syntax

MySQL

SELECT A.column1, B.column2
FROM table_name A
JOIN table_name B ON A.common_field = B.common_field
WHERE condition;

Use table aliases (like A and B) to distinguish the same table used twice.

The JOIN condition defines how rows relate to each other within the same table.

Examples

Find each employee's manager by joining the employees table to itself.

MySQL

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

List pairs of products in the same category but different products.

MySQL

SELECT p1.product_name, p2.product_name
FROM products p1
JOIN products p2 ON p1.category = p2.category
WHERE p1.id <> p2.id;

Sample Program

This creates an employees table with manager relationships and shows each employee with their manager's name.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
ORDER BY e1.id;

Important Notes

Always use aliases to avoid confusion when joining a table to itself.

LEFT JOIN is useful if some rows may not have a matching related row.

Self JOINs can help find hierarchical or related data within one table.

Summary

Self JOIN lets you join a table to itself using aliases.

It helps find relationships between rows in the same table.

Use it to compare or connect data inside one table.