🔁 SQL `SELF JOIN` – Joining a Table to Itself

The SELF JOIN in SQL is a powerful technique that allows you to join a table to itself, as if you had two identical tables. It’s especially useful for hierarchical relationships, such as employees and their managers, products and their variants, or customers and their referrals.

📘 What is a SQL SELF JOIN?

A SELF JOIN is a regular SQL JOIN that joins a table with itself.
To make this work, we use table aliases to differentiate between the two instances of the same table.

📌 Think of it as:

“Compare or relate rows within the same table.”

🧾 Syntax of SQL SELF JOIN

SELECT A.column_name, B.column_name
FROM table_name A
JOIN table_name B
ON A.common_column = B.common_column;

📌 A and B are aliases of the same table.
They act like two separate tables, but they’re actually the same one.

✅ Real-Life Example: Employees and Managers

Let’s say you have this employees table:

emp_id	emp_name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2

You want to list each employee along with their manager’s name.

SELECT 
  E.emp_name AS employee,
  M.emp_name AS manager
FROM employees E
LEFT JOIN employees M
ON E.manager_id = M.emp_id;

🧾 Result:

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice
Dave	Bob

🎯 A classic use case for SELF JOIN – showing a hierarchy.

🧠 When to Use SELF JOIN in SQL

💼 Employees reporting to managers (organizational hierarchy)
🛍️ Products and product variants or parent-child SKUs
🌳 Category and sub-category structure
💰 Transactions and refunds linked within the same table
👥 Users referring other users (referral relationships)

⚠️ Key Tips for Using SELF JOIN

💡 Best Practice	✅ Recommendation
Use clear aliases	Alias your tables like `A` and `B`
Avoid column name conflicts	Always qualify column names with alias
Understand relationships	Ensure one column refers to another row
Use LEFT JOIN if needed	Helps include rows without matches (e.g., no manager)

🔍 Another SELF JOIN Example: Customer Referrals

SELECT 
  c1.customer_name AS customer,
  c2.customer_name AS referred_by
FROM customers c1
LEFT JOIN customers c2
ON c1.referred_by = c2.customer_id;

This shows which customer referred whom.

🎯 SELF JOIN vs Other Joins

Join Type	Joins…	Use Case
INNER JOIN	Two different tables	Match data across tables
LEFT JOIN	Two different tables	Preserve unmatched left data
SELF JOIN	Table with itself	Show relationships within a table

📝 Summary

A SELF JOIN lets you relate rows within the same table
It’s great for hierarchies, self-referencing relationships, and comparisons
Always use aliases to distinguish table instances

Self Join

🔁 SQL SELF JOIN – Joining a Table to Itself