? MySQL SELF JOIN

? Quick Overview

A SELF JOIN is a regular join where a table is joined with itself. It's useful for comparing rows within the same table — for example, listing employees with their managers (both rows in the same employees table). When using a SELF JOIN you alias the table to differentiate each instance.

? Key Concepts

Aliasing: Give the same table two different aliases (e.g., e1 and e2) to reference separate row instances.
Join condition: The ON clause references columns that relate rows within the same table (e.g., e1.manager_id = e2.employee_id).
Use-cases: Hierarchical data (managers/employees), referrals, products in the same category, etc.

? Syntax / Theory

? View Code Example

-- Syntax: alias the same table twice and join on the related column
SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

? Code Example(s)

? View Code Example

-- Example: Get employees and their managers (same table with self join)
SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;

? View Code Example

-- Example: List products in the same category 
(exclude identical products)
SELECT p1.product_name AS Product1, p2.product_name AS Product2
FROM products p1
INNER JOIN products p2
ON p1.category_id = p2.category_id
WHERE p1.product_id != p2.product_id;

? Live Output / Explanation

Explanation

Employees & managers: When you run the employee query, e1 refers to the employee row, and e2 refers to the manager row. The join condition pairs each employee with their manager's record.
Products in same category: The second query pairs each product with other products in the same category; the WHERE p1.product_id != p2.product_id ensures you don't pair a product with itself.
Result shape: SELF JOIN results look like any joined query — rows combining columns from both aliases.

? Tips & Best Practices

Always alias the table when using a SELF JOIN to avoid ambiguous column references.
Use descriptive aliases (e.g., employee & manager or p1 & p2).
Double-check the join condition to avoid Cartesian products or incorrect pairings.
When filtering, be explicit to avoid accidentally excluding desired relationships.

? Try It Yourself / Practice Tasks

Write a query to list each employee and their manager's email using a SELF JOIN on the employees table.
Use SELF JOIN to find product pairs that share the same supplier and category.
Find all referrals in a customers table where a customer referred another customer (use a SELF JOIN).