Who is each employee's manager? The answer lives in the SAME employees table — the manager_id column points at another row of it. To ask that question, the table has to meet itself at the join keyboard. That trick, the self join, unlocks hierarchies, row-to-row comparisons, and sequence analysis.
What you'll learn
What
A self join joins a table to itself. You list the same table twice under different aliases — say e for the employee role and m for the manager role — and write an ON condition connecting a row to a DIFFERENT row of the same table. The engine treats the two aliases as two independent tables that happen to share data.
Why
Plenty of real-world structure is row-to-row within one table: org charts (employee→manager), referrals (user→referrer), product substitutions, consecutive events by the same customer. Without self joins you'd export the table twice and merge it in pandas; with them, one query answers 'compare this row to that row' natively.
Where it's used
Org-chart reporting, referral attribution, month-over-month comparisons before window functions, duplicate detection, and 'customers who bought X also bought Y' pair analysis.
Where this runs in production
Accounts, territories, and user roles reference parent rows in their own tables; reporting rollups resolve manager and parent-territory names via self joins.
Each user row stores referred_by_user_id; joining users to itself attributes signups to referrers and measures viral loops.
Pair analysis joins the order_items table to itself on order_id with product_a < product_b to count co-purchased product pairs at scale.
Imagine photocopying the company directory and laying both copies on your desk — the left copy for looking people up as employees, the right copy for looking the SAME people up as managers. When you read 'Priya, manager_id 17', you keep a finger on Priya in the left copy and flip the right copy to id 17 to find her manager's name. The aliases e and m are your two copies; the data is identical, the roles differ.
e and m scan the same rows; what differs is the role each alias plays in the ON condition. Name aliases after roles (e/m, child/parent, first_order/second_order) — never t1/t2. In a self join, readable aliases are the difference between obvious and unreviewable SQL.
SELECT e.name AS employee,
m.name AS manager
FROM employees e
INNER JOIN employees m
ON m.employee_id = e.manager_id;The hierarchy pattern: each employee row carries a foreign key (manager_id) pointing at another row of the same table — a self-referencing foreign key. The ON clause connects child to parent. Note both output columns come from the same physical column (name) via different aliases — aliasing the outputs is essential too.
The CEO has manager_id NULL — no row matches, so an INNER self join silently removes the CEO from the org report. If the hierarchy's root(s) must appear, use LEFT JOIN employees m … and the CEO comes back with a NULL manager name. This is the LEFT-vs-INNER decision from the previous lessons wearing an org chart.
With o2.order_id <> o1.order_id, each pair appears twice — (A,B) and (B,A). Using < instead (o1.order_id < o2.order_id) keeps each unordered pair exactly once AND excludes self-pairs in one stroke. For 'find duplicates' and 'co-purchase pairs' queries, < is the professional idiom; <> doubles your counts.
The employees table participates twice under different aliases. Click each node to see how a single physical table plays both sides of the join.
employees(employee_id, name, manager_id): (1,'Sofia',NULL), (2,'Priya',1), (3,'Marco',1), (4,'Lena',2). Show each employee with their manager's name.
Priya's manager_id 1 finds Sofia's row on the m side; same for Marco. Lena's manager_id 2 finds Priya. Sofia (manager_id NULL) matches nothing and is absent — the INNER-drops-the-root effect. Three rows out of four employees.
employee | manager ----------+--------- Priya | Sofia Marco | Sofia Lena | Priya (3 rows)
Your task
HR needs a salary sanity report: every employee who earns MORE than their direct manager. From employees(employee_id, name, manager_id, salary), return the employee's name (employee), their salary (emp_salary), the manager's name (manager), and the manager's salary (mgr_salary). Fill in the blanks.
employee | emp_salary | manager | mgr_salary ----------+------------+---------+------------ Lena | 99000 | Priya | 95000 (1 row)
Write your solution in the editor on the right, then hit Run.
Why are table aliases mandatory in a self join?
In FROM employees e JOIN employees m ON m.employee_id = e.manager_id, what does a result row represent?
A pair-generation self join uses ON b.id <> a.id instead of ON b.id > a.id. What is the consequence?
An org-chart query uses INNER JOIN employees m ON m.employee_id = e.manager_id. Which employees are missing from the output?
Why is the query exploding, and what is the pragmatic fix?
Detect duplicate customer records: from customers(customer_id, email, name), return pairs of DIFFERENT customer_ids sharing the same email — each pair once, lower id first. Columns: id_a, id_b, email.
What is a self join, and what kinds of questions require one?
A self join joins a table to itself under two aliases, treating the same data as two logical tables playing different roles. It's required whenever the relationship you're querying is row-to-row within one table. The archetypes: hierarchies stored as self-referencing foreign keys (employee→manager, category→parent-category), where the join resolves a pointer into readable parent attributes; row comparison within a grouping (two orders by the same customer, duplicate emails); and pair generation (products co-occurring in a basket). Mechanically nothing changes versus a normal join — same ON semantics, same fan-out rules, same INNER/LEFT decision — but aliases become mandatory for disambiguation, and I name them for roles (e/m, child/parent) rather than t1/t2 so the query reads as the business question it answers.
You write an employee-manager report and the CEO is missing. What happened, and what does the fix look like?
The report used an INNER self join — FROM employees e JOIN employees m ON m.employee_id = e.manager_id — and the CEO's manager_id is NULL. NULL matches nothing in an equality predicate, and INNER drops non-matching left rows, so the root of the hierarchy disappeared. The fix is a LEFT JOIN from the employee side, which preserves every employee and NULL-fills the manager columns for roots; presentation-wise I'd wrap the manager name in COALESCE(m.name, '—') or a '(top level)' label. The deeper lesson is that this is the standard LEFT-vs-INNER decision applied to hierarchies: any tree stored with a nullable parent key has roots, and every hierarchy query must consciously decide whether roots belong in the result. I'd also flag the symmetric issue for multi-level rollups: walking two levels (manager's manager) means chaining another LEFT self join per level, or moving to a recursive CTE when depth is unknown.
How would you find duplicate records in a table using a self join, and why use < rather than <> in the condition?
Join the table to itself on the attribute that defines duplication — say email — and require different primary keys: FROM customers a JOIN customers b ON b.email = a.email AND b.customer_id > a.customer_id. Each duplicate cluster then emits every pair exactly once. The > (or <) inequality does two jobs that <> only half-does: it excludes self-matches AND canonicalizes pair orientation, so (1,3) appears but (3,1) doesn't; with <>, every pair shows up twice and any downstream counts are doubled. For clusters larger than two, the pair output grows quadratically — n duplicates yield n(n−1)/2 pairs — so at scale I'd usually switch to GROUP BY email HAVING COUNT(*) > 1 to find the clusters first, then self-join or window-function only within the flagged emails. That two-step keeps the query cheap and gives dedup tooling a cluster id to work with.
Common Mistakes to Avoid
1) Forgetting aliases — the query won't even parse with the same table named twice. 2) Reversing the hierarchy ON clause (e.employee_id = m.manager_id asks 'who reports to e', not 'who manages e') — always read the foreign key direction aloud. 3) Using INNER when hierarchy roots (NULL parent keys) must appear — the CEO silently vanishes; use LEFT. 4) Pair generation with <> instead of <, doubling every co-occurrence count. 5) Running pair self joins without profiling group sizes — one 5,000-item basket contributes ~12.5M pairs and turns a seconds query into an hours query.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: a self join using a phone-book photocopy analogy of your own.' • 'Quiz me: 5-row employees table, I predict the exact output of INNER vs LEFT self joins.' • 'Show me the two directions of the org-chart ON clause and how to tell them apart.' • 'When should I use a window function instead of a self join? Give me three cases each.' • 'Interview mode: have me write a duplicate-detection query and critique my inequality choice.'
Glossary
Self join — a join of a table to itself via two aliases. Alias role — the logical part (employee vs manager) an alias plays; same data, different function. Self-referencing foreign key — a column pointing at the primary key of the same table (manager_id → employee_id). Hierarchy root — a row with a NULL parent key (the CEO); dropped by INNER self joins. Parent/child — the two ends of a hierarchy edge. Pair generation — self-joining on a shared attribute to enumerate row pairs. Canonical pair — an unordered pair kept in exactly one orientation via a < condition. Self-pair — a row matched with itself; excluded by any strict inequality. Span of control — direct-report count per manager; a grouped self join. Quadratic blow-up — pair output growing as n(n−1)/2 with group size n. Recursive CTE — the tool for unknown-depth hierarchy walks (Advanced module).
Recommended Resources
• Docs: PostgreSQL tutorial — 'Joins Between Tables' includes the canonical self-join weather example. • Read: 'Hierarchical Data in SQL' (adjacency lists vs other models) to see where self joins fit among tree designs. • Practice: model your own team as an employees table with manager_id, then write: everyone+manager (LEFT), spans of control, and anyone earning above their manager. • Next in DSM: you can join a table to itself and one table to another — Multiple Joins chains three or more tables and teaches you to control fan-out across the chain.
Recap
✓ A self join lists one table twice under mandatory, role-named aliases — same data, two logical tables. ✓ Hierarchies ride self-referencing foreign keys; ON m.employee_id = e.manager_id resolves child → parent, and reversing it flips the question. ✓ INNER self joins drop hierarchy roots (NULL parent keys); LEFT keeps them with NULL-filled parent columns. ✓ Row-to-row comparisons join on a shared attribute plus an inequality; < generates each unordered pair once and bans self-pairs. ✓ Pair output grows quadratically with group size — profile the biggest groups before running pair analysis. ✓ Sequences and previous-row comparisons are usually better served by window functions; hierarchies and pairing stay self-join territory. Next up: Multiple Joins. Two tables at a time is training wheels — real questions span customers, orders, order items, AND products. Next you'll chain joins across three or more tables and keep the row counts honest at every step.
Run your code to see the output here.