Self-Join for Hierarchies
Self-Join for Hierarchies is a SQL clause in the JOIN Types category. Self-join pattern for hierarchical data (org chart, categories, threaded comments). The syntax is SELECT e.name AS emp, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;. It returns self-joined hierarchy. A typical example: -- Org chart (level 2): SELECT e.name AS employee, m.name AS manager, d.name AS department FROM employees e LEFT JOIN employees m ON e.manager_id = m.id LEFT JOIN departments d ON e.dept_id = d.id ORDER BY d.name, m.name, e.name; -- Category hierarchy: SELECT c1.name AS category, c2.name AS subcategory FROM categories c1 JOIN categories c2 ON c1.id = c2.parent_id WHERE c1.parent_id IS NULL; -- Top-level categories with their subcategories A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type. A close relative is LEFT JOIN, which returns ALL rows from the left table and matching rows from the right. Unmatched right rows show NULL.