BeginnerSQL · 25 min

Self-Join: Beginner

Self-join is the first SQL pattern that breaks the model new data engineers learn in their first SQL class. Every join you have written so far joins two different tables. A self-join joins one table to itself. The query looks weird the first time you see it. The pattern shows up constantly: employee-and-manager, current-and-previous, comparing pairs of rows in the same table. The interviewer is not testing whether you know the syntax. They are testing whether you can read a question and recognize that 'compare row A to row B in the same table' is the shape of the answer.

Spot the trigger phrases that turn an English question into a self-join

Write the canonical self-join with aliases that read like English

Avoid the duplicate-pair bug that catches every first-time self-join writer

Pick between a self-join and a window function based on the comparison shape

Two Rows, Same Table, Different Meaning

Daily Life

Interviews

Recognize that questions comparing pairs within a table (manager/employee, previous/next, duplicate detection) require self-joins.

Here is the question that throws candidates the first time they see it: 'find every employee who earns more than their manager.' The employees table has employee_id and manager_id. There is no separate managers table. The candidate freezes for thirty seconds, then tries to write a CASE expression. The CASE expression cannot work, because comparing two rows in the same table requires the row twice. That is what a self-join is. You list the same table twice in the FROM clause, with different aliases, and join it to itself.

You are being tested on a self-join when you hear:

▸"employees who earn more than their manager"
▸"customers in the same city as another customer"
▸"find pairs of products that ..."
▸"detect duplicates where two rows share ..."
▸"compare each row to the previous one"
▸Any question where the comparison is between two rows of the same table

What the interviewer is actually testing

This question screens whether you can hold a small but unfamiliar shape in your head. The candidate who pauses, says 'I need to join the employees table to itself, with one alias for the employee and one for the manager,' has already cleared the bar. The candidate who tries to twist this into a single-table query with subqueries is signaling that they have not internalized that a row can play two roles at once. The interviewer is watching whether you reach for the right shape, not whether the syntax compiles on the first try.

The mental shift that solves the question:

▸One table appears twice in the FROM clause with two different aliases
▸Each alias represents a different role (employee vs manager)
▸The join condition expresses the relationship between the two roles
▸The same row can play both roles in different result rows

Say the word 'self-join' out loud the moment you spot the pattern. The single utterance tells the interviewer you parsed the prompt correctly. Many candidates begin writing SQL before they name the technique, which forces the interviewer to figure out the approach from the syntax. Naming it first is what gets you credit for pattern recognition.

The 10-second decision

Read the question. If the comparison is between two rows that both live in the same table, the answer is a self-join. The signal is usually a phrase like 'compared to,' 'more than,' 'in the same X as,' 'previous,' or 'paired with.' When you see one of those phrases applied to two rows from one table, your first sentence is: 'I'll join the table to itself with aliases for the two roles, and the join condition will be the relationship between them.' Say that sentence before you touch the keyboard.

•Weak opening

Tries to write a single SELECT with a subquery in the WHERE
Gets stuck because both rows are needed at once
Asks 'is there a managers table?' (there is not)
Loses two minutes to confusion before the right shape emerges

✓Strong opening

Says 'this is a self-join, the employees table aliased twice'
Writes FROM employees e JOIN employees m ON e.manager_id = m.employee_id
Names the two aliases clearly: e for employee, m for manager
Has working SQL on the page within ninety seconds

Why companies care

Self-joins power every reporting query that involves a relationship within a single entity table. Manager chains. Friend graphs. Product variants. Same-day conversion pairs. Duplicate detection. If you cannot write a self-join, an entire class of business questions is closed to you. That is why this is a common opening question, especially when the interviewer wants to know whether you can move past simple single-table queries.

Aliasing the Table Twice (e and m)

Daily Life

Interviews

Write a self-join with clear aliases and a precise ON clause that avoids Cartesian explosions.

The query has the same shape every time. Same table in the FROM clause twice, with two different aliases. Join condition expresses the relationship between the two roles. SELECT pulls columns from both aliases. The aliases are not decoration; they are the only way SQL knows which copy of the table you are talking about in each column reference.

The query you should be able to write from memory

	/* Employees who earn more than their manager */
	SELECT
	e.employee_id,
	e.name AS employee_name,
	e.salary AS employee_salary,
	m.name AS manager_name,
	m.salary AS manager_salary
	FROM employees AS e
	INNER JOIN employees AS m
	ON e.manager_id = m.employee_id
	WHERE e.salary > m.salary

Read this query out loud while you type it. The FROM clause lists employees twice. Alias e is the employee row. Alias m is the manager row. The join condition says 'pair each employee with the row whose employee_id equals this employee's manager_id.' The WHERE filters down to pairs where the employee's salary exceeds the manager's. Without the aliases, SQL cannot tell which copy of the table you mean when you reference salary.

Why the aliases must hint at the role:

▸e/m beats t1/t2: the reader sees "employee, manager" at a glance
▸parent/child for trees; prev/curr for sequences; before/after for snapshots
▸Aliasing is part of the answer; the interviewer reads it as part of the query

Why the aliases must be short and meaningful

Aliasing employees as e and m is the convention. Aliasing them as t1 and t2 works mechanically but is harder to read. The convention is to use one letter that hints at the role: e for employee, m for manager, c for child, p for parent. The interviewer is reading your query in real time. They will notice if your aliases are anonymous numbers; they will notice if they are meaningful letters. The naming is part of the answer.

In an interview, never name your aliases t1 and t2. Always pick aliases that hint at the row's semantic role. e/m, parent/child, before/after, left/right, prev/curr. When the interviewer reviews your query later, the aliases tell them you thought about the two roles separately, not just mechanically aliased to make the syntax work.

Walk through the trace, including the CEO question

Suppose the employees table has four rows. Alice (id=1, manager_id=NULL, salary=200k). Bob (id=2, manager_id=1, salary=180k). Carol (id=3, manager_id=1, salary=220k). Dave (id=4, manager_id=2, salary=150k). The join pairs Bob with Alice (Bob's manager_id is 1), Carol with Alice, and Dave with Bob. The WHERE keeps only pairs where the employee's salary exceeds the manager's. Carol (220k) earns more than Alice (200k), so Carol's row appears. Bob earns less than Alice, so Bob does not. Dave earns less than Bob, so Dave does not. The result is one row: Carol. Alice does not appear because her manager_id is NULL; the join condition e.manager_id = m.employee_id does not match anything for Alice's row, and NULL does not equal anything in SQL's three-valued logic. Alice is silently dropped from the result. This is correct behavior for this question. If the interviewer asks 'what about the CEO?', say: 'The CEO has no manager, so they are dropped by the inner join. If we wanted to include them, we would use a LEFT JOIN and handle the NULL.'

TIP

Before you write SELECT, name the two roles out loud. 'I'm aliasing employees as e for the employee, and as m for the manager. The join condition pairs each employee with their manager.' This sentence shows the interviewer that you see the two roles as distinct, not as a mechanical syntax requirement.

Writing the Self-Join Condition

Daily Life

Interviews

Prevent duplicate pairs using a < condition (a.id < b.id) and explain why to the interviewer.

The interviewer will give you a question that sounds slightly different: 'find pairs of employees in the same department.' Now the comparison is symmetric. Alice paired with Bob is the same pair as Bob paired with Alice. If you write the obvious join, you get every pair twice, plus every employee paired with themselves. The fix is a single inequality in the join condition. Most candidates miss it the first time. The interviewer is watching for it.

The bug, stated plainly

Joining employees to themselves on department alone produces the cartesian product within each department. If a department has three employees, you get nine rows: Alice-Alice, Alice-Bob, Alice-Carol, Bob-Alice, Bob-Bob, Bob-Carol, Carol-Alice, Carol-Bob, Carol-Carol. The three self-pairs are nonsense. The six remaining rows are three real pairs counted twice in opposite orders. You need three rows out, you have nine, and the candidate who does not catch this returns nine.

The fix: the < condition

Add the condition e1.employee_id < e2.employee_id to the ON clause. This guarantees three things at once. First, the inequality is strict, so e1 cannot equal e2 (no self-pairs). Second, only one of (Alice, Bob) and (Bob, Alice) survives, because if Alice's id is smaller, the pair appears as (Alice, Bob) and the reversed pair is filtered. Third, the same logic generalizes to any symmetric comparison: < gives you each unordered pair exactly once.

What the < condition guarantees

▸Self-pairs are dropped (the inequality is strict)
▸Each unordered pair appears exactly once
▸Generalizes to any symmetric comparison
▸Works on any unique column; employee_id, customer_id, transaction_id all qualify

	/* Pairs of employees in the same department, each pair once */
	SELECT
	e1.name AS employee_a,
	e2.name AS employee_b,
	e1.department_id
	FROM employees AS e1
	INNER JOIN employees AS e2
	ON e1.department_id = e2.department_id
	AND e1.employee_id < e2.employee_id

Walk through the trace. Engineering has Alice (id=1), Bob (id=2), Carol (id=3). The join with the < condition produces (Alice, Bob), (Alice, Carol), (Bob, Carol). Three pairs, each direction kept exactly once. Without the < condition you would have nine rows. Without any condition past department_id you would have nine rows plus the three self-pairs. The < is the smallest possible change that makes the query correct.

•Without the < condition

Returns 9 rows for a 3-person department
Includes Alice-Alice, Bob-Bob, Carol-Carol
Includes both (Alice, Bob) and (Bob, Alice)
Symmetric pair counts inflate by a factor of 2

✓With the < condition

Returns 3 rows for a 3-person department
Drops self-pairs because the inequality is strict
Keeps each unordered pair exactly once
Generalizes to any symmetric comparison

When to use != vs <

If the question asks for ordered pairs (every Alice-paired-with-Bob AND every Bob-paired-with-Alice as separate rows), use != instead of <. The != drops only the self-pairs and keeps both directions. Most interview questions about pairs are symmetric, so < is the right default. The interviewer will tell you when ordered pairs are needed; the language is usually 'every directed pair' or 'every (a, b) such that...'.

Asking the interviewer 'are the pairs directional, or do we want each pair once?' is the move that earns the credit. Many candidates write the query and notice the duplicate problem when they trace the output. The candidates who ask first get full marks for the question without ever writing the wrong query.

TIP

The < pattern is not specific to employee ids. It works on any column with a total ordering: customer_id, transaction_id, timestamp. The principle is: when comparing pairs in a self-join, pick a column that is unique per row and use strict inequality to dedupe. The same trick deduplicates duplicate detection queries, friend-of-friend graphs, and 3Sum-style range queries.

"Earns More Than Their Manager"

Daily Life

Interviews

Decide when LAG/LEAD or ROW_NUMBER replaces a self-join and when the self-join is the only clean option.

The interviewer's follow-up: 'now compare each row to the previous one.' Two solutions work. The self-join, with the inequality moved into the join condition. The window function, with LAG over the ordered partition. Knowing both, and knowing when each wins, is the move.

The two solutions, side by side

Question: for each transaction, return the previous transaction's amount for the same account. Self-join solution: join transactions to itself on account_id, with the second copy filtered to the immediately preceding txn. Window function solution: LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_date).

	SELECT
	curr.account_id,
	curr.txn_date,
	curr.amount AS current_amount,
	prev.amount AS prior_amount
	FROM transactions curr
	LEFT JOIN transactions prev
	ON prev.account_id = curr.account_id AND prev.txn_date < curr.txn_date AND NOT EXISTS(SELECT 1 FROM transactions other WHERE other.account_id = curr.account_id AND other.txn_date < curr.txn_date AND other.txn_date > prev.txn_date) ;


	SELECT
	account_id,
	txn_date,
	amount AS current_amount,
	LAG(amount) OVER(PARTITION BY account_id ORDER BY txn_date) AS prior_amount
	FROM transactions ;

The window function version is shorter, faster, and easier to read. The self-join version is longer because finding 'the immediately previous row' requires either a NOT EXISTS subquery or a correlated subquery that picks the max txn_date that is less than the current one. The window function does this in one expression: LAG.

When the window function wins

Adjacent-row comparisons. 'Previous row,' 'next row,' 'row five back.' Window functions were designed for exactly this shape. They are typically faster because the engine can stream through the partition in sorted order without materializing the join. Reach for the window function first; reach for the self-join only if the comparison is not adjacent.

When the self-join wins

Non-adjacent comparisons. 'Each row vs all earlier rows.' 'Each customer vs all other customers in the same city.' 'Each transaction vs the matching one a year ago.' These are not 'the previous row'; they are 'a row defined by a condition.' Window functions cannot express arbitrary join conditions; self-joins can. If the comparison cannot be reduced to 'N rows ago in the ordered partition,' the self-join is the right reach.

•Self-join is the better choice

Comparison condition is not a fixed row offset
The other row is matched by a join predicate, not by position
The query needs to join on multiple columns simultaneously
The engine has weak window-function support

✓Window function is the better choice

Comparison is 'previous row' or 'N rows ago' in a sorted partition
The other row is at a fixed position relative to the current row
Performance matters and you have a modern columnar engine
The output should keep one row per input row, not produce pairs

Stating which tool you would reach for and why is what the interviewer scores. Many candidates can write both. Few can articulate the difference. The articulation is the answer; the SQL is just the proof.

At Google in 2018, an internal people-analytics dashboard had a 'pairs of employees in the same office who have collaborated on a doc' query that returned exactly twice the expected pair count for two weeks before anyone noticed. The query joined employees to employees on office_id and a doc co-edit predicate; the author had used != to drop self-pairs but had not added the < inequality, so every collaboration pair appeared as both (Alice, Bob) and (Bob, Alice). The metric on the dashboard was 'unique cross-collaboration pairs per office,' and that number was inflated 2x. The fix was the single character < replacing !=. The runbook now reads 'any self-join producing pair counts uses strict inequality on a unique column, and pair-count metrics should be sanity-checked against a known small office before shipping.' Naming this bug by its shape, not by 'I used the wrong operator,' is what tells the interviewer you've seen it on a real dashboard.

Situation	Phrasing that flatlines	Phrasing that lands
You see a same-table comparison	"I'll join the table to itself."	"This is a self-join. I'll alias employees as e for the employee and m for the manager; the join condition encodes the relationship between the two roles."
The interviewer asks 'find pairs in the same department'	"I'll join on department_id."	"Symmetric pairs need an inequality. e1.employee_id < e2.employee_id added to the ON clause drops self-pairs and keeps each unordered pair once."
The interviewer asks 'what about the CEO'	"They're not in the result."	"The inner join drops them because manager_id is NULL and NULL doesn't equal anything in SQL's three-valued logic. If the report should include the CEO, switch to LEFT JOIN and let m.name appear as NULL."
The interviewer asks 'now find each row vs the previous'	"I'll add a row-offset condition."	"For an adjacent-row comparison, LAG over a partition is cleaner than a self-join with a NOT EXISTS subquery. The self-join wins when the comparison isn't 'previous row' but 'a row defined by a join predicate.'"
The interviewer asks for 'all reports, any depth'	"I'll chain joins."	"Self-join is fixed-depth. Unbounded hierarchy needs a recursive CTE: anchor on direct reports, recursive step joins reports back to employees to walk one more level, terminates when the recursive step returns zero rows."

Why a Subquery Won't Capture Two Roles

Daily Life

Interviews

Handle recursive manager chains, multi-level comparisons, and know when to pivot to a recursive CTE.

The interviewer escalates: 'now find every employee under a given manager, recursively, including indirect reports.' A plain self-join can only walk one level. Two levels takes two joins. Three levels takes three joins. Recursive depth takes a recursive CTE. The question is testing whether you know the boundary between a self-join and a recursive query, and when to cross it.

When to switch from chained self-join to recursive CTE:

▸Fixed depth (1-3 levels named in the prompt) → chained self-join
▸Unbounded depth (entire subtree, arbitrary depth) → recursive CTE
▸WITH RECURSIVE walks one level per iteration until no new rows
▸Most modern engines support it; mention the dialect constraint on older MySQL

The two-level self-join

If the question stops at two levels (manager and the manager's manager), chain two self-joins. The aliases multiply, but the query is still flat.

	/* Employees, their manager, and their manager's manager */
	SELECT
	e.name AS employee,
	m.name AS manager,
	gm.name AS grand_manager
	FROM employees AS e
	LEFT JOIN employees AS m
	ON e.manager_id = m.employee_id
	LEFT JOIN employees AS gm
	ON m.manager_id = gm.employee_id

Two LEFT JOINs because employees with no manager (or whose manager has no manager) should still appear with NULLs. Three aliases, one per level. This pattern stops working when the hierarchy is unbounded. Five-deep, ten-deep, or 'arbitrarily deep' org charts cannot be hand-chained.

When to reach for the recursive CTE

If the question says 'all reports, direct and indirect' or 'the entire subtree' or 'arbitrary depth,' a recursive CTE is the correct tool. The recursive CTE starts with the root row, then repeatedly joins the previous result to the employees table to walk one more level, stopping when no new rows are added.

	/* All employees under a given manager, any depth */
	WITH RECURSIVE reports AS (
	/* Anchor: direct reports of the target manager */
	SELECT
	employee_id,
	name,
	manager_id,
	1 AS depth
	FROM employees
	WHERE manager_id = 42

	UNION ALL

	/* Recursive step: reports of the previous level */
	SELECT
	e.employee_id,
	e.name,
	e.manager_id,
	r.depth + 1
	FROM employees AS e
	INNER JOIN reports AS r
	ON e.manager_id = r.employee_id
	)

	SELECT
	*
	FROM reports
	ORDER BY depth, name

The anchor query finds the direct reports of the target manager. The recursive step finds the reports of the previous level. The engine repeats the recursive step until it returns zero new rows. The depth column is incremented at each step, so you can see how far down the tree each employee sits. Most modern engines (Postgres, SQL Server, BigQuery, Snowflake) support WITH RECURSIVE. MySQL added it in 8.0; if the interview is on an older MySQL, mention this constraint.

What the interviewer is testing at this level

The escalation tests whether you know the limits of the tool you started with. Self-joins are excellent for fixed-depth comparisons; they are wrong for unbounded depth. The candidate who hand-writes seventeen LEFT JOINs has not reached for the right tool. The candidate who says 'this is unbounded, so I'll use a recursive CTE' has shown that they map the question's shape to the SQL feature that handles it.

•Use a chained self-join when

The depth is fixed and small (one or two levels)
The question explicitly names the levels (manager, manager's manager)
You want a flat row with columns for each level
The engine is old and may not support recursive CTEs

✓Use a recursive CTE when

The depth is unbounded or unknown ahead of time
The output is one row per descendant, with a depth column
The traversal needs to compute aggregates per level
The engine supports WITH RECURSIVE

The closing you can memorize

After solving the problem, close with a three-sentence wrap. 'I used a self-join with two aliases for the two roles, and the join condition expressed the relationship between them. I would use the < inequality on a unique column when the comparison is symmetric, to avoid counting each pair twice. For unbounded depth, I would switch to a recursive CTE instead of chaining joins.' Three sentences. Each names a different rubric item. The shape generalizes to any pair-or-hierarchy SQL question.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a Snowflake data engineering phone screen. The interviewer asks: 'Find every employee who earns more than their manager.'

You say: 'This is a self-join. I will alias the employees table twice: e for the employee, m for the manager.'

You write the query: FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary.

You trace through an example: Bob has manager Alice. The join pairs Bob's row with Alice's row using e.manager_id = m.employee_id. The WHERE keeps rows where the employee outearns their manager.

Follow-up: 'What about the CEO?' You say: 'The CEO has no manager, so e.manager_id is NULL. The inner join drops them. If we wanted to include them, LEFT JOIN and handle the NULL on the m side.'

KEY TAKEAWAYS

When the comparison is between two rows of the same table, phrased as 'compared to', 'more than', 'in the same X as', or 'previous', the answer is a self-join with one alias per role.

Aliases carry the semantics: e for employee and m for manager tell the interviewer you separated the two roles, while t1 and t2 work mechanically and read as anonymous.

For symmetric pairs, add e1.employee_id < e2.employee_id to the ON clause: the strict inequality kills self-pairs and keeps each unordered pair exactly once. Use != only when the question wants both directions.

Reach for LAG or LEAD on adjacent-row comparisons, where a self-join needs a NOT EXISTS subquery to isolate the immediately preceding row. Keep the self-join for comparisons defined by a condition rather than by row distance.

Self-joins walk fixed depth only: one level is one join, two levels is two joins. Unbounded depth like 'all reports, direct and indirect' means a RECURSIVE CTE with an anchor query and a step that joins the previous level back to the table.

The table joins itself when the question compares rows to other rows

Category: SQL
Difficulty: beginner
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: Two Rows, Same Table, Different Meaning, Aliasing the Table Twice (e and m), Writing the Self-Join Condition, "Earns More Than Their Manager", Why a Subquery Won't Capture Two Roles

Lesson Sections

Two Rows, Same Table, Different Meaning (concepts: sqlSelfJoin)
What the interviewer is actually testing This question screens whether you can hold a small but unfamiliar shape in your head. The candidate who pauses, says 'I need to join the employees table to itself, with one alias for the employee and one for the manager,' has already cleared the bar. The candidate who tries to twist this into a single-table query with subqueries is signaling that they have not internalized that a row can play two roles at once. The interviewer is watching whether you reac
Aliasing the Table Twice (e and m) (concepts: sqlSelfJoin)
The query has the same shape every time. Same table in the FROM clause twice, with two different aliases. Join condition expresses the relationship between the two roles. SELECT pulls columns from both aliases. The aliases are not decoration; they are the only way SQL knows which copy of the table you are talking about in each column reference. The query you should be able to write from memory Read this query out loud while you type it. The FROM clause lists employees twice. Alias e is the emplo
Writing the Self-Join Condition (concepts: sqlSelfJoin)
The interviewer will give you a question that sounds slightly different: 'find pairs of employees in the same department.' Now the comparison is symmetric. Alice paired with Bob is the same pair as Bob paired with Alice. If you write the obvious join, you get every pair twice, plus every employee paired with themselves. The fix is a single inequality in the join condition. Most candidates miss it the first time. The interviewer is watching for it. The bug, stated plainly The fix: the < condition
"Earns More Than Their Manager" (concepts: sqlSelfJoin)
The interviewer's follow-up: 'now compare each row to the previous one.' Two solutions work. The self-join, with the inequality moved into the join condition. The window function, with LAG over the ordered partition. Knowing both, and knowing when each wins, is the move. The two solutions, side by side Question: for each transaction, return the previous transaction's amount for the same account. Self-join solution: join transactions to itself on account_id, with the second copy filtered to the i
Why a Subquery Won't Capture Two Roles (concepts: sqlRecursiveCte)
The interviewer escalates: 'now find every employee under a given manager, recursively, including indirect reports.' A plain self-join can only walk one level. Two levels takes two joins. Three levels takes three joins. Recursive depth takes a recursive CTE. The question is testing whether you know the boundary between a self-join and a recursive query, and when to cross it. The two-level self-join If the question stops at two levels (manager and the manager's manager), chain two self-joins. The