Basic Self Join - Employee Manager Hierarchy

basic

Show employees with their manager names using self join

-- Self join to show employee-manager relationships

SELECT

e1.employee_id,

e1.employee_name AS employee,

e1.job_title,

e2.employee_name AS manager,

e2.job_title AS manager_title

FROM employees e1

LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id

ORDER BY e2.employee_name, e1.employee_name;

-- Alternative with INNER JOIN (excludes CEO/top level)

SELECT

e1.employee_name AS employee,

e2.employee_name AS manager

FROM employees e1

INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

Shows employees with their managers, including CEO with NULL manager (LEFT JOIN) or excluding CEO (INNER JOIN)

LEFT JOIN preserves employees without managers (CEO). INNER JOIN excludes them. Use different aliases (e1, e2) to distinguish roles.

All

Comparative Self Join - Salary Analysis

intermediate

Find employees who earn more than their manager

-- Find employees earning more than their manager

SELECT

e1.employee_name AS employee,

e1.salary AS employee_salary,

e2.employee_name AS manager,

e2.salary AS manager_salary,

(e1.salary - e2.salary) AS salary_difference

FROM employees e1

INNER JOIN employees e2 ON e1.manager_id = e2.employee_id

WHERE e1.salary > e2.salary

ORDER BY salary_difference DESC;

Returns employees who earn more than their managers with salary comparison

INNER JOIN excludes employees without managers. WHERE compares salaries between employee and manager.

All

Complex Self Join - Social Network Friends