What is a self join and when would you use it?

A self join is joining a table to itself using different aliases. Use it for hierarchical relationships (employee-manager), comparing rows within the same table, or finding relationships within the same entity. Common examples: organizational charts, social networks, product recommendations.

Why do you need different aliases in self joins?

Aliases distinguish between different instances of the same table. Without aliases, the database cannot determine which table instance you are referencing. For example, e1 represents employees and e2 represents managers in an employee-manager self join.

When should you use LEFT JOIN vs INNER JOIN in self joins?

Use LEFT JOIN when some records may not have a matching relationship (like CEO having no manager). Use INNER JOIN when all records must have a relationship. LEFT JOIN preserves all records from the left table, INNER JOIN only returns records with matches.

Write a query to show all employees with their managers, including employees who have no manager.

SELECT

e1.employee_name AS employee,

COALESCE(e2.employee_name, 'No Manager') AS manager

FROM employees e1

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

ORDER BY e1.employee_name;

LEFT JOIN preserves all employees. COALESCE handles NULL managers (CEO/top-level employees).

Find employees who earn 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

FROM employees e1

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

WHERE e1.salary > e2.salary

ORDER BY (e1.salary - e2.salary) DESC;

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

Show the organizational hierarchy with employee levels (CEO = level 1, direct reports = level 2, etc.).

-- Using recursive CTE for multi-level hierarchy

WITH RECURSIVE employee_hierarchy AS (

-- Base case: CEO (no manager)

SELECT employee_id, employee_name, manager_id, 1 as level

FROM employees

WHERE manager_id IS NULL

UNION ALL

-- Recursive case: employees with managers

SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1

FROM employees e

INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id

)

SELECT employee_name, level

FROM employee_hierarchy