Q39: What is a Self-JOIN and when is it used?
A Self-JOIN joins a table to itself. It is commonly used to query hierarchical data stored in a single table, such as employee-manager relationships or sequential state updates.
Finding Employees and Their Managers via Self-JOIN
SELECT
e.first_name AS employee_name,
m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;💡 Aliasing the same table twice (e for employee, m for manager) allows comparing linked rows within a single table.
Key Takeaways
- Self-JOINs join a table to itself using distinct table aliases.
- Ideal for evaluating intra-table hierarchies and sequential records.