sqlANYself-joinhierarchy
sql
SELECT e.employee_id,
e.name AS employee_name,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;Alias the same table twice so each role is clear.
Self Joins and Hierarchies
SQL self join examples for managers, duplicates, comparisons, and basic hierarchy modeling.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Self joins
Join a table to itself for hierarchies and comparisons.
Find duplicate emails with self join
Compare rows in the same table using inequality to avoid mirrored duplicates.
sqlANYduplicatesself-join
sql
SELECT a.user_id, b.user_id, a.email
FROM users a
JOIN users b
ON a.email = b.email
AND a.user_id < b.user_id;The `<` condition avoids returning both `(a,b)` and `(b,a)`.
sqlANYpairingself-join
sql
SELECT a.id, b.id, a.event_date
FROM events a
JOIN events b
ON a.event_date = b.event_date
AND a.id < b.id;Useful for pairing or clash detection inside one table.
Hierarchy alternatives
When a self join is enough and when you need more.
Use self join for one-level hierarchy
Great for direct parent relationships like employee → manager.
textANYrecursive-ctehierarchy
text
One-level lookup: self join
Deep tree traversal: recursive CTEA single self join handles immediate parent or sibling checks. For full ancestor paths, use recursive CTEs.
More in SQL Joins
SQL Join Patterns and Pitfalls
Common SQL join mistakes, reusable templates, and correctness rules for production queries.
Join Performance, EXPLAIN, and Optimization
SQL join tuning with indexes, plan inspection, row reduction, and troubleshooting guidance.
Aggregation After Joins and NULL Handling
Counting, summing, pre-aggregating, and handling NULLs correctly after one-to-many and outer joins.
Semi-Joins and Anti-Joins
High-value EXISTS, IN, NOT EXISTS, and LEFT JOIN IS NULL patterns for membership and missing-row queries.
Many-to-Many Joins and Bridge Tables
Many-to-many SQL join patterns with bridge tables, aggregation, filtering, and index guidance.
INNER, LEFT, RIGHT, and FULL OUTER JOIN Patterns
Practical SQL patterns for inner, outer, and reconciliation-style joins, including full outer join emulation.