SQL Self Join: Syntax, Use Cases, and Interview Questions

-- Basic self join: employees and their managers
SELECT
  e.employee_id,
  e.name AS employee_name,
  e.manager_id,
  m.name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

-- Keep all employees, show NULL for top-level managers
SELECT
  e.employee_id,
  e.name AS employee_name,
  m.name AS manager_name  -- NULL for CEO / top-level
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Interview note: Always clarify whether the question requires all employees or only those with managers. This determines whether you use INNER JOIN or LEFT JOIN. Asking this question shows the interviewer you think about data completeness.

-- Multi-level hierarchy: employee -> manager -> director
SELECT
  e.name AS employee,
  m.name AS manager,
  d.name AS director
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees d ON m.manager_id = d.employee_id;

-- Recursive CTE for arbitrary-depth hierarchy
WITH RECURSIVE org_chart AS (
  -- Base case: top-level (no manager)
  SELECT employee_id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join children to parents
  SELECT e.employee_id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY depth, name;

Interview tip: Start with a self join for 1-2 levels. If the interviewer asks 'what if the hierarchy is N levels deep,' switch to a recursive CTE. Showing both approaches demonstrates range.

-- Compare each day's revenue to the previous day
SELECT
  curr.date,
  curr.revenue AS today_revenue,
  prev.revenue AS yesterday_revenue,
  curr.revenue - prev.revenue AS daily_change,
  CASE
    WHEN prev.revenue = 0 THEN NULL
    ELSE ROUND(100.0 * (curr.revenue - prev.revenue) / prev.revenue, 1)
  END AS pct_change
FROM daily_revenue curr
INNER JOIN daily_revenue prev
  ON curr.date = prev.date + INTERVAL '1 day'
ORDER BY curr.date;

-- Find consecutive logins (user logged in two days in a row)
SELECT DISTINCT a.user_id, a.login_date
FROM user_logins a
INNER JOIN user_logins b
  ON a.user_id = b.user_id
  AND a.login_date = b.login_date + INTERVAL '1 day';

-- Window function approach (cleaner for ordered comparisons)
SELECT
  date,
  revenue AS today_revenue,
  LAG(revenue) OVER (ORDER BY date) AS yesterday_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_revenue
ORDER BY date;

The window function is shorter and avoids the join entirely. But the self join is more flexible: it can compare non-adjacent rows, handle irregular intervals, and join on multiple conditions that do not fit the strict row-ordering that LAG/LEAD require.

-- Employees who share the same manager
SELECT
  e1.name AS employee_1,
  e2.name AS employee_2,
  e1.manager_id
FROM employees e1
INNER JOIN employees e2
  ON e1.manager_id = e2.manager_id
  AND e1.employee_id < e2.employee_id  -- unique pairs only
ORDER BY e1.manager_id, e1.name;

-- Products in the same category with similar prices (within 10%)
SELECT
  p1.name AS product_1,
  p2.name AS product_2,
  p1.category,
  p1.price AS price_1,
  p2.price AS price_2
FROM products p1
INNER JOIN products p2
  ON p1.category = p2.category
  AND p1.product_id < p2.product_id
  AND ABS(p1.price - p2.price) <= p1.price * 0.10
ORDER BY p1.category, p1.name;

-- Customers who purchased the same product
SELECT
  o1.customer_id AS customer_1,
  o2.customer_id AS customer_2,
  o1.product_id,
  p.name AS product_name
FROM orders o1
INNER JOIN orders o2
  ON o1.product_id = o2.product_id
  AND o1.customer_id < o2.customer_id
INNER JOIN products p ON o1.product_id = p.product_id
ORDER BY o1.product_id;