1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

A SQL self join joins a table to itself so you can compare one row with another row in the same table. Learn the employee-manager pattern, unique pairs, day-over-day changes and the mistakes that double your results, with queries we ran in SQLite.

9 min read
SQL self join explained: employee-manager, pairs and row comparisons

Key takeaways

  • A SQL self join is an ordinary join where both sides are the same table, told apart with aliases such as e and m.
  • Use INNER JOIN to drop rows with no match, and LEFT JOIN to keep them, such as the top manager.
  • For pairs, a.id < b.id gave 6 rows, a.id <> b.id gave 12 duplicates, and no filter gave 18 rows in our test.
  • Index the column you join on, such as manager_id, so lookups use a search instead of a scan.

Most joins combine two different tables: orders with customers, posts with authors. But some questions live inside one table. Who is this employee’s manager? Which two customers live in the same city? How much did today’s value change from yesterday’s? For all of these you need a SQL self join.

We ran every query below in SQLite 3.50.4 through Python 3.13, and the results are copied from that run. The syntax is standard SQL and works the same way in PostgreSQL, MySQL and SQL Server, although we only executed it in SQLite. If joins in general are new to you, start with our SQL joins explained guide.

What is a self join?

There is no SELF JOIN keyword. A self join is a regular INNER JOIN or LEFT JOIN in which the same table appears twice. Because both copies have the same column names, you must give each one an alias:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Read it as two separate tables: e holds the employees and m holds possible managers. The ON clause links an employee’s manager_id to a manager’s id. The relationship is just a foreign key that points back into its own table, as explained in our primary key vs foreign key guide.

The sample table

We used one small table so every result is easy to check by eye:

CREATE TABLE employees(
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id),
  city TEXT,
  salary INT
);
INSERT INTO employees VALUES
 (1,'Asha',NULL,'Pune',200000), (2,'Ravi',1,'Pune',120000),
 (3,'Li',1,'Delhi',125000),     (4,'Sam',2,'Pune',90000),
 (5,'Noor',2,'Delhi',130000),   (6,'Tomas',3,'Delhi',80000);

Asha is at the top with no manager. Ravi and Li report to Asha, Sam and Noor report to Ravi, and Tomas reports to Li.

Employee and manager side by side

The inner join from above returned five rows:

employee manager
Ravi Asha
Li Asha
Sam Ravi
Noor Ravi
Tomas Li

Six people are in the table, but only five rows came back. Asha is missing because nobody sits above the top of the tree: manager_id is NULL, and an inner join only keeps rows with a match.

Keeping the top boss with LEFT JOIN

To list everyone, switch to a left join and replace the missing name with a label:

SELECT e.name AS employee, COALESCE(m.name, '(none)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This returned all six rows, with Asha’s manager shown as (none). The choice between inner and left join is a business decision: an org chart needs everyone, while a “who reports to whom” list can skip the top. COALESCE is covered in depth in our SQL NULL values guide.

Comparing values across rows

Once both rows sit side by side, you can compare any of their columns. A classic interview question asks for employees who earn more than their manager:

SELECT e.name, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
('Noor', 130000, 'Ravi', 120000)

Only Noor qualified. The same shape answers many real questions: products priced above their parent category’s average, tickets reopened by the same user who closed them, or test cases that take longer than the suite they belong to.

Counting direct reports

Turn the join around to count how many people each person manages. Put the manager on the left so people with zero reports still appear:

SELECT m.name, COUNT(e.id) AS reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id
ORDER BY reports DESC;

Asha and Ravi each had 2, Li had 1, and Sam, Noor and Tomas had 0. Counting e.id rather than * is what makes the zeros correct, because COUNT(*) would count the unmatched row as 1. Our GROUP BY and HAVING guide covers this pattern in detail.

Finding unique pairs

Which employees share a city? Joining on city alone pairs each person with themselves and lists every pair twice. We counted the rows each filter produced:

Join condition Rows What you get
a.city = b.city 18 Self-pairs and both orders
... AND a.id <> b.id 12 Every pair twice (A-B and B-A)
... AND a.id < b.id 6 Each pair exactly once
SELECT a.name, b.name, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.id < b.id;

The six pairs were Li-Noor, Li-Tomas and Noor-Tomas in Delhi, and Asha-Ravi, Asha-Sam and Ravi-Sam in Pune. The < trick is the standard way to de-duplicate pairs and is also handy for finding duplicate customer records with the same email.

Comparing a row with the previous row

Self joins can also compare rows by date. We joined daily temperature readings to the reading from one day earlier:

SELECT t.day, t.temp, y.temp AS prev, ROUND(t.temp - y.temp, 1) AS change
FROM readings t
LEFT JOIN readings y ON y.day = date(t.day, '-1 day');
day temp prev change
2026-11-01 21.0 NULL NULL
2026-11-02 23.5 21.0 2.5
2026-11-03 22.0 23.5 -1.5
2026-11-04 26.0 22.0 4.0
2026-11-06 25.0 NULL NULL

Notice 6 November: there was no reading on the 5th, so the self join found nothing and returned NULL. The window function LAG(temp) OVER (ORDER BY day) gave 26.0 for that row instead, because it looks at the previous row, not the previous day. Neither is wrong; they answer different questions. Pick the self join when calendar gaps matter and window functions when you want the previous record.

Two hops and beyond

Join the table a third time to find each employee’s manager’s manager:

SELECT e.name, m.name AS manager, g.name AS grand
FROM employees e
JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees g ON m.manager_id = g.id;

Sam, Noor and Tomas got Asha as their grand-manager, while Ravi and Li got NULL. Each extra level needs another join, so for trees of unknown depth use a recursive CTE instead. Ours built full paths such as Asha > Ravi > Sam in one query; see our SQL CTE guide for the syntax.

Performance and indexing

A self join is as fast as any other join, and it follows the same indexing rules. We checked SQLite’s query plan:

  • Looking up each employee’s manager used SEARCH m USING INTEGER PRIMARY KEY, because the join hits the primary key.
  • Listing a manager’s reports needs the reverse direction. After we added CREATE INDEX ix_mgr ON employees(manager_id), the plan showed SEARCH e USING INDEX ix_mgr instead of a scan.

On large tables, that index is the difference between milliseconds and a full table scan for every manager. Our SQL indexes guide explains how to read these plans.

Common mistakes

  • Forgetting aliases. Without e and m, the database cannot tell which copy a column belongs to and reports an ambiguous column error.
  • Using INNER JOIN by habit. Rows with no partner, such as the CEO, vanish without warning.
  • Duplicate pairs. Use a.id < b.id, not <>, when order does not matter.
  • Counting with COUNT(*). After a left join it counts unmatched rows as 1. Count a column from the right-hand table.
  • Mixing up direction. e.manager_id = m.id finds managers; m.manager_id = e.id finds reports. Name your aliases after their role to keep this clear.

Final thoughts

The SQL self join looks odd at first, but it is just a join with two aliases for one table. Use it for hierarchies, pairs and comparisons between rows. Choose left or inner join on purpose, de-duplicate pairs with <, index the join column, and move to window functions or recursive CTEs when the question is about the previous row or a tree of unknown depth.

Frequently asked questions

What is a self join in SQL?

It is a join where a table is joined to itself, using two different aliases, so you can compare or combine rows from the same table, such as an employee and their manager.

Is there a SELF JOIN keyword?

No. You write a normal INNER JOIN or LEFT JOIN and list the same table twice with different aliases.

Why are my self join results duplicated?

Joining on a shared value matches each row with itself and lists each pair in both orders. Add a condition such as a.id < b.id to keep each pair once.

Should I use a self join or LAG?

Use LAG to compare with the previous row in a sort order. Use a self join when you need a specific related row, such as the same date minus one day, where gaps should return NULL.

How do I make a self join faster?

Index the columns in the join condition, such as manager_id, and check the query plan to confirm the database searches the index instead of scanning the table.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →