Employee-manager hierarchies use self joins. Social networks use self joins for friend relationships. E-commerce uses self joins for product recommendations ("customers who bought X also bought Y").

Corporate Organizational Chart

HR department needs to generate organizational charts showing employee-manager relationships for performance reviews and reporting structures.

Self join to connect employees with their managers and build hierarchical reporting structure

-- Complete organizational structure with hierarchy levels

WITH RECURSIVE org_chart AS (

-- CEO level (no manager)

SELECT

employee_id,

employee_name,

job_title,

manager_id,

salary,

1 as level,

employee_name as hierarchy_path

FROM employees

WHERE manager_id IS NULL

UNION ALL

-- All other levels

SELECT

e.employee_id,

e.employee_name,

e.job_title,

e.manager_id,

e.salary,

oc.level + 1,

oc.hierarchy_path || ' > ' || e.employee_name

FROM employees e

INNER JOIN org_chart oc ON e.manager_id = oc.employee_id

)

SELECT

employee_name,

job_title,

level,

hierarchy_path,

salary

FROM org_chart

ORDER BY level, employee_name;

Enables clear reporting structures, facilitates performance reviews, and supports succession planning decisions.

All