Self join for hierarchical data in SQL

Introduction

We use self join to connect rows in the same table that have a parent-child relationship. This helps us see how items are linked in a hierarchy.

When you want to find an employee's manager in the same employee table.

When you want to list categories and their parent categories in a product catalog.

When you want to show parts and their sub-parts in a manufacturing list.

When you want to display family trees from a single table of people.

Syntax

SQL

SELECT child.column, parent.column
FROM table AS child
JOIN table AS parent ON child.parent_id = parent.id;

Use table aliases (like child and parent) to refer to the same table twice.

The join condition links the child row to its parent row using IDs.

Examples

This finds each employee and their manager from the same employees table.

SQL

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

This lists categories with their parent categories.

SQL

SELECT c.name AS category, p.name AS parent_category
FROM categories AS c
JOIN categories AS p ON c.parent_id = p.id;

Sample Program

This creates a simple employee table with managers, then shows each employee with their manager's name. If an employee has no manager, it shows NULL.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

Important Notes

Use LEFT JOIN if some rows might not have a parent (like top-level items).

Self joins can be used multiple times to go up several levels in a hierarchy.

Summary

Self join connects rows in the same table to show parent-child links.

Use table aliases to keep the query clear.

It helps display hierarchical data like managers, categories, or parts.