self joins

A self join is just joining a table to itself. Nothing new syntax wise, but it trips people up because it feels weird at first.

when would you even need this

When rows in a table point to other rows in the same table. The classic one is employees, every employee has a manager_id, and the manager is also an employee in the same table.

employee_id | name   | manager_id
1           | Vikram | NULL       <- the boss
2           | Anita  | 1          <- reports to Vikram
3           | Rohit  | 2          <- reports to Anita

the trick: two aliases

Pretend you have two copies of the table, one called e (employees) and one called m (managers).

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

Read the ON like "the employee's manager_id is the manager's employee_id".

Vikram isn't in the result because his manager_id is NULL, no match. Use LEFT JOIN if you want him:

SELECT e.name AS employee, COALESCE(m.name, '-') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

who earns more than their manager?

Classic interview question. Once you have both rows side by side it's easy:

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Nobody in our data, which is honestly realistic. Change Meera's salary to 160000 and try again.

comparing rows with each other

Self joins aren't only for hierarchies. Find pairs of employees in the same department:

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.employee_id < b.employee_id;

Why < and not <>? With <> you'd get (Rohit, Meera) and also (Meera, Rohit), plus nobody gets paired with themselves either way. < keeps each pair only once.

Another one, customers from the same city:

SELECT a.first_name, b.first_name, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.customer_id < b.customer_id;

going more than one level up

Employee → manager → manager's manager, just join again:

SELECT e.name, m.name AS manager, mm.name AS skip_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.employee_id
LEFT JOIN employees mm ON m.manager_id = mm.employee_id;

This gets ugly fast if you don't know how deep the tree goes. That's what recursive CTEs are for (advanced topic).

stuff that trips people up

• forgetting aliases, you literally can't do a self join without them.
• mixing up which side is which in ON, e.employee_id = m.manager_id gives you reports instead of managers.
• getting every pair twice with <>.
• INNER join dropping the top of the hierarchy.

practice

Check queries.sql.

-- self joins
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT e.name AS employee, COALESCE(m.name, '-') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department AND a.employee_id < b.employee_id;

SELECT e.name, m.name AS manager, mm.name AS skip_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.employee_id
LEFT JOIN employees mm ON m.manager_id = mm.employee_id;


-- practice ---------------------------------------------------------

-- 1. for each manager, how many people report directly to them
-- 2. employees who were hired before their manager (would be weird but check)
-- 3. pairs of products in the same category where one costs more than double the other
-- 4. pairs of orders placed by the same customer, showing both order_ids and the days between them
-- 5. employees who earn the same salary as someone else


-- answers ----------------------------------------------------------

-- 1
SELECT m.name AS manager, COUNT(e.employee_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY direct_reports DESC;

-- 2
SELECT e.name, e.hire_date, m.name AS manager, m.hire_date AS manager_hired
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.hire_date < m.hire_date;

-- 3
SELECT a.product_name AS pricey, b.product_name AS cheap, a.category
FROM products a
JOIN products b ON a.category = b.category AND a.price > 2 * b.price;

-- 4
SELECT a.customer_id, a.order_id AS first_order, b.order_id AS later_order,
       b.order_date - a.order_date AS days_between
FROM orders a
JOIN orders b ON a.customer_id = b.customer_id AND a.order_date < b.order_date
ORDER BY a.customer_id;

-- 5
SELECT DISTINCT a.name, a.salary
FROM employees a
JOIN employees b ON a.salary = b.salary AND a.employee_id <> b.employee_id;
-- Pooja and Dev, both 70000