MySQL Self Join

Understanding the Self Join

A self join isn't a special SQL keyword — it's a regular INNER JOIN or LEFT JOIN where both sides of the join refer to the same table. Because a table can't be listed twice under its own name in a single query, you give it two different aliases so MySQL can tell which occurrence of a column you mean. Once aliased, the two references behave like independent tables that happen to share the same underlying rows.

When You Need One

Self joins show up whenever rows in a table relate to other rows in that same table. The classic example is an employees table where each row stores a manager_id that points to another employee's id. To print an employee alongside their manager's name, you need to look up a second row in the same table — exactly what a self join does.

• Modeling hierarchies, such as employees who report to managers who are themselves employees
• Comparing rows against each other, such as products that share the same price
• Detecting duplicate records that differ only by primary key
• Finding relationships between rows, such as consecutive orders placed by the same customer

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Notice that the table name employees appears twice, once aliased as e for the employee row and once as m for the manager row. Every column reference after that point must be prefixed with e. or m. so MySQL knows which occurrence you mean — without the aliases, the query would be ambiguous and fail to parse.

SELECT e.name AS employee_name, e.salary, m.name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

SELECT p1.product_name AS product_a, p2.product_name AS product_b, p1.price
FROM products p1
JOIN products p2 ON p1.price = p2.price AND p1.product_id < p2.product_id;