SQL
Query Hierarchical Data with Self-Joins (e.g., Managers and Employees)
Learn to use self-joins in SQL to query hierarchical relationships within a single table, like finding employees and their managers, for efficient data traversal.
SELECT
e.employee_id,
e.employee_name AS EmployeeName,
e.manager_id,
m.employee_name AS ManagerName
FROM
employees e
LEFT JOIN
employees m ON e.manager_id = m.employee_id
ORDER BY
e.employee_name;
How it works: This snippet uses a `LEFT JOIN` on the `employees` table with itself (a self-join). It joins an employee's `manager_id` with another employee's `employee_id` to retrieve the manager's name. This technique is crucial for traversing hierarchical structures within a single table, like organizational charts, allowing for clear display of employee-manager relationships.