What is a SELF JOIN and when would you use it?

Question

Accepted Answer

38
   What is a SELF JOIN and when would you use it?
  Level: Intermediate to Advanced — Tests your ability to handle hierarchical and comparative data.

  A SELF JOIN is a join where a table is joined with itself. It's not a special keyword — it's a regular join (INNER or OUTER) where both sides reference the same table, distinguished by using different table aliases.

  
     Why aliases are essential: Since you're referencing one table twice, you must use aliases (e.g., a and b) so MySQL can tell the two "copies" apart. Without them, the query is ambiguous.
  

   Common Use Cases
  
     Hierarchical data: Employee–Manager relationships (both stored in one table).
     Comparing rows: Finding pairs of employees in the same department or city.
     Finding duplicates: Matching rows with identical values.
     Sequential data: Comparing a row to the previous/next one.
  

   Classic Example: Employee–Manager
  Consider an employees table where each employee has a manager_id that points to another employee's emp_id in the same table:
  
    
      
        
          emp_id
          name
          manager_id
        
      
      
        
          1
          Ajay (Manager)
          NULL
        
        
          2
          Rumman
          1
        
        
          3
          Krushna
          1
        
      
    
  

   Quick Example
-- Show each employee alongside their manager's name
SELECT
    e.name  AS employee,
    m.name  AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- 'e' = employee copy, 'm' = manager copy of the SAME table

-- Find pairs of employees in the same department
SELECT
    a.name AS employee_1,
    b.name AS employee_2,
    a.dept_id
FROM employees a
INNER JOIN employees b
    ON a.dept_id = b.dept_id
    AND a.emp_id < b.emp_id;   -- avoids duplicate pairs & self-match

  
     Note the a.emp_id < b.emp_id trick: It prevents an employee from matching themselves and stops the same pair from appearing twice (e.g., "A–B" and "B–A").
  

  
     Interviewer tip: The one-liner they want — "A SELF JOIN joins a table to itself using aliases, mainly to handle hierarchical data like employee–manager relationships or to compare rows within the same table."

What is a SELF JOIN and when would you use it?

Answer with Explanation

What is a SELF JOIN and when would you use it?

Common Use Cases

Classic Example: Employee–Manager

Quick Example