🔄 SELF JOIN – Joining a Table with Itself

(because sometimes your data needs to have a conversation with itself)

🔧 Quick Syntax

SELECT a.columns, b.columns
FROM table_name a, table_name b
WHERE a.column = b.column;

This says:
“Join the table with itself, using two different aliases (a and b),
and match based on a common column.”

👀 Imagine You Have a employees Table Like This:

Here, each employee has a manager_id that refers to another emp_id in the same table.
We want to get a list of employees and their managers — but both are in the same table!

🔧 SELF JOIN Syntax

SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.emp_id;

We’re telling SQL:
“Join the employees table with itself using aliases a and b
where the manager_id in a matches the emp_id in b.”

✅ Result

Notice:
– Olivia has no manager (NULL), so she doesn’t show up here.
– Mason and Zoe both have Olivia as their manager.
– Ethan reports to Mason.

🔁 Why Use SELF JOIN?

• When you have hierarchical relationships in your data (e.g., managers, parent-child relationships)
• When data points in a table refer to other data points in the same table
• Useful in organizational structures, family trees, or any situation where one row’s data refers to another row in the same table

🧃 Recap

• SELF JOIN allows you to join a table with itself using aliases
• Great for handling hierarchical data, like employees and their managers
• You match a column in the first instance of the table with a column in the second instance (e.g., manager_id = emp_id)