Self Joins
Overview
A Self Join isn't a new SQL keyword; it's a technique. It is simply a standard INNER or LEFT join where Table A and Table B are literally the exact same table! This is required when dealing with hierarchical data (data that references itself). The classic example is an employees table where every employee has a manager_id. Since the manager is also an employee, their data lives in the same table. To get the employee's name and their manager's name side-by-side, you must join the table to itself.
Syntax
-- A Self Join REQUIRES the use of Aliases to work!
-- We pretend the table exists twice: once as 'emp', once as 'mgr'
SELECT
emp.first_name AS "Employee Name",
mgr.first_name AS "Manager Name"
FROM employees emp
-- We LEFT JOIN so the CEO (who has no manager) isn't deleted!
LEFT JOIN employees mgr
ON emp.manager_id = mgr.id;Common Pitfalls
- Forgetting to use distinct table aliases (like
e1ande2). If you writeFROM employees JOIN employees, the SQL engine will crash because it has no idea which copy of the table you are referring to in theONclause. - Creating infinite loops in recursive structures. If Employee A's manager is Employee B, and Employee B's manager is mistakenly set to Employee A, a standard self-join will execute fine, but advanced Recursive CTEs will infinitely loop and crash.
Interview Questions
Join the table to itself on emp.manager_id = mgr.id, and then add a filter: WHERE emp.salary > mgr.salary.
Real-World Example
Finding pairs of duplicate data. For example, finding all instances where two completely different users registered with the exact same IP address.
SELECT
u1.username AS user_a,
u2.username AS user_b,
u1.ip_address
FROM users u1
-- Join the table to itself where the IP matches...
JOIN users u2 ON u1.ip_address = u2.ip_address
-- ...BUT ensure we don't match the user to themselves! (id < id)
WHERE u1.id < u2.id;Check Your Knowledge
Test your understanding of Self Joins with these quick questions.