Two Rows Playing Different Roles
Concepts covered: Self-Join
The question rarely arrives with the word 'join' in it. It arrives as a comparison: 'who out-earns their boss', 'which employees sit in Carol's department', 'find pairs of accounts registered with one email'. Each one asks about a relationship between 2 rows, and both rows live in the same table. The interviewer is checking whether you notice that fact before you start typing, because a candidate who treats it as a single-row filter writes a WHERE clause that cannot be finished.
The question in its disguises
- 'Which employees earn more than the person they report to?'
- 'Show me every employee alongside their manager's name.'
- 'Which tickets were escalated from a ticket that is still open?'
- 'Find every product variant priced below its parent product.'
- 'List each store with the quarterly revenue of the hub it belongs to.'
Every one of these has a column on one row that points at another row of the same table: manager_id points at an employee_id, escalated_from_ticket_id points at a ticket_id, parent_product_id points at a product_id. The word 'their' or 'its' in the question is the tell. 'Their manager' means a second row, found by following the pointer, and the answer needs a value from that second row sitting beside a value from the first.
One row, 2 roles
| employee_id | name | manager_id | salary |
|---|---|---|---|
| 1 | Alice | NULL | 200000 |
| 2 | Bob | 1 | 180000 |
| 3 | Carol | 1 | 220000 |
| 4 | Dave | 2 | 150000 |
Read Bob's row twice. In the question 'does Bob earn more than his manager', Bob is the employee: his manager_id is 1, which points at Alice, and the comparison is 180000 against 200000. In the question 'does Dave earn more than his manager', Bob is the manager: Dave's manager_id is 2, which points at Bob, and the comparison is 150000 against 180000. The same physical row plays the employee role in one comparison and the manager role in another. Nothing in the table marks a row as a manager; the role comes from which side of the pointer the row sits on.
A WHERE clause evaluates one row at a time, so when it is looking at Carol's row it can see salary = 220000 and manager_id = 1, and nothing else. The number it needs, Alice's 200000, is 2 rows away. No expression over Carol's own columns can reach it. That is the wall candidates hit when they try a CASE expression or a plain filter: the manager's pay sits on a second row of the same table, and the row being tested does not contain the value it is being compared to.
The shape, in 4 facts
- One table is listed twice in the FROM clause, under 2 different aliases
- Each alias plays one role: one alias is the employee, the other is the manager
- The join condition is the pointer on one role matched to the key on the other
- A single stored row can be the employee in one output row and the manager in another
What the interviewer scores in your first sentence
The score at this point is for pattern recognition, and it is awarded in the first sentence you say. The sentence that clears it: 'The manager's pay is on a different row, so I will join employees to itself, one alias for the employee and one for the manager.' That names the shape, names the 2 roles and tells the interviewer the join condition is coming. Candidates who skip this sentence and start typing force the interviewer to reverse-engineer the plan from the syntax, and a typo in the ON clause then reads as confusion about the approach rather than a slip of the fingers.
- The answer that stalls
- Scans the column list for manager_salary and finds nothing
- Asks whether there is a managers table (there is not)
- Tries a CASE expression or a filter over a single row
- Loses 2 minutes before the second copy of the table appears
- The answer that clears the bar
- Says 'self-join' within the first 10 seconds
- Names the roles: one alias is the employee, the other the manager
- States that manager_id on one side matches employee_id on the other
- Has a running query on the screen inside 90 seconds
Outside the interview, org charts and bill-of-materials trees have the same shape, as do ticket escalation chains and referral programs. So do product variants, and so does any table with a column that points back into itself. An engineer who cannot write a self-join cannot answer questions about any of those, which is why the question is asked early. It screens whether you can hold one table in 2 roles in your head, and it does so with 1 table and 4 rows.
About this lesson
This section is part of the Self-Join: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.