Why a Subquery Returns One Role

Concepts covered: Correlated Subquery, Scalar Subquery, Self-Join

The other route candidates take to this question is a subquery: keep the employee's row in the outer query and look up the manager's salary with a nested SELECT. It is a legitimate answer to the names-only version, and knowing exactly where it stops is what separates a candidate who chose it from one who fell into it.

-- Correlated subquery: one lookup per employee row
SELECT
  e.name,
  e.salary
FROM employees e
WHERE e.salary > (
  SELECT m.salary
  FROM employees m
  WHERE m.employee_id = e.manager_id
);

For each outer row, the inner SELECT runs with that row's manager_id plugged in and returns one value: the manager's salary. Carol's row gets 200000 back, 220000 > 200000 holds, Carol is kept. Alice's row plugs in NULL, the inner query finds no row and returns NULL, 200000 > NULL is unknown, Alice is dropped. The result matches the self-join exactly. But look at what the output row holds: Carol's name and Carol's salary. The 200000 that decided the row was compared and then thrown away, so the output carries only the employee role.

The follow-up that breaks it

'Show me the manager's name beside each employee.' With the self-join that is one more column in the SELECT list, m.name, because the manager's whole row is already attached. With the subquery there is no m in the outer query to reference. The manager's row existed only inside the parentheses, for the duration of one comparison, and it is gone. Getting the name back means a second subquery in the SELECT list, with the same correlation repeated.

-- One subquery per manager column you need
SELECT
  e.name,
  e.salary,
  (SELECT m.name   FROM employees m WHERE m.employee_id = e.manager_id) AS manager_name,
  (SELECT m.salary FROM employees m WHERE m.employee_id = e.manager_id) AS manager_salary
FROM employees e
WHERE e.salary > (
  SELECT m.salary FROM employees m WHERE m.employee_id = e.manager_id
);

The query now has 3 nested SELECTs that find the same manager row, and each may return only 1 column, because a scalar subquery produces 1 value. Add the manager's hire date and there are 4. Every one of them repeats the correlation m.employee_id = e.manager_id, so a typo in 1 of them silently returns the wrong manager's value in 1 column while the others are right. The self-join states that condition once, in the ON clause, and then every column of the manager's row is available by prefixing it with m.

There is a cost story too, and it is worth one sentence. On paper a correlated subquery runs the inner query once per outer row, and while most optimizers rewrite a simple one into a join, 3 of them against the same table may not all be rewritten. The self-join reads the table twice and matches once. On 4 rows nothing is measurable; on a 1,200-row org chart it is still fine; the point to make in the room is that the join expresses the work once and leaves the optimizer less to guess at.

Self-join

• Both rows sit on 1 output row; any column from either is e.col or m.col
• The relationship is stated once, in ON
• Adding a manager column is 1 line in the SELECT list
• Reads as 'employee paired with manager', which is how the question was asked

Correlated scalar subquery

• Output holds the outer row only; the inner row is consulted and discarded
• The relationship is restated inside every subquery
• Adding a manager column is another nested SELECT
• Reads as 'employee, with a lookup', which hides that 2 roles exist

When the subquery is the right answer

A subquery wins when the question only ever wants 1 role in the output. 'Which employees have at least 1 direct report?' is WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id), and no column from the report's row is wanted, so there is nothing for a join to carry. The rule to state out loud: if the answer shows columns from both roles, join; if it shows 1 role and the other only decides which rows survive, use a subquery or EXISTS, which is often clearer.

The sentence that shows you chose

If you reach for the subquery, say why: 'The question wants only the employee's name, so a correlated subquery is enough; if we need the manager's columns too, I would switch to a self-join so both rows are on the same output row.' That tells the interviewer you know the limit before they test it. If you reach for the join, the mirror sentence works: 'I am joining so the manager's columns are available for the follow-ups.' If you say neither, expect a follow-up aimed at that gap.

Do

• Say 'self-join' and name the 2 roles before writing FROM
• Alias by role with e and m instead of t1 and t2
• Put the pointer on the child alias: e.manager_id = m.employee_id
• Trace 1 row through the ON clause out loud, then 1 through the WHERE
• Mention the NULL manager_id and name the join type it implies

Avoid

• Write a WHERE comparison while employees is still listed only once in FROM
• Leave any column unprefixed in a self-join
• Trust the row count to catch a reversed join condition
• Stack scalar subqueries when the output needs columns from both roles
• Wait for the interviewer to ask about the CEO

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.