Aliasing the Table Twice (e and m)

Concepts covered: Self-Join, Inner Joins, Join Cardinality

The FROM clause is where the second copy comes from. Writing FROM employees e JOIN employees m tells the database to read the employees table twice and to call one reading e and the other m. From that point every column reference in the query has to say which reading it means: e.salary is the salary on the employee's row, m.salary is the salary on the manager's row. The aliases are the mechanism, and without them the query cannot be written at all.

-- Pair each employee with their manager's row
SELECT
  e.employee_id,
  e.name   AS employee_name,
  m.name   AS manager_name,
  e.salary AS employee_salary,
  m.salary AS manager_salary
FROM employees e
JOIN employees m
  ON e.manager_id = m.employee_id;

The query returns 3 rows, one per employee who has a manager. Alice's manager_name and manager_salary show up twice, once on Bob's row and once on Carol's, because 2 employees point at her. Alice does not appear as employee_name at all, since her manager_id is NULL and matches nothing; the next section covers that row. Look at what each output row now holds: a salary from the employee role and a salary from the manager role, side by side. The comparison the question asked for is one WHERE clause away.

It helps to know what the engine considered before it kept those 3. Joining 2 copies of a 4-row table gives 16 candidate pairings, every e row against every m row. The ON condition tests each pairing and keeps only those where the pointer on the e side lands on the employee_id of the m side: Bob with Alice, Carol with Alice, Dave with Bob. None of the other 13 pairings reach the output; they fail the test, and that includes each row paired with itself. Because employee_id is unique, no e row can pass against 2 different m rows, so the join never multiplies an employee into several output rows.

Unqualified columns fail as ambiguous

Drop the prefix and write SELECT name, salary FROM employees e JOIN employees m ON ... and the database refuses: column reference "salary" is ambiguous. Both copies have a salary column, and the engine has no way to know whether you mean the employee's or the manager's. In an ordinary 2-table join the ambiguity only arises for columns that happen to share a name. In a self-join every column shares a name with its twin, so every reference needs its alias. Interviewers watch for this: a candidate who prefixes every column has internalised that there are 2 rows in play.

Postgres rejects an unqualified column in a self-join with an ambiguity error, and so do MySQL and SQLite. The loud error helps, because a version of this bug that ran silently would be far harder to catch.

Aliases that name the role

The convention for this question is e for the employee row and m for the manager row. The engine would run the query identically with t1 and t2, since it does not care what the aliases are called. The interviewer does, because they are reading the query as you type it and the aliases are the only words on the screen that say which copy is which. With e and m, the line WHERE e.salary > m.salary reads as 'employee salary exceeds manager salary' with no translation. With t1 and t2 the reader has to scroll back to the ON clause to work out which copy holds the pointer, and so do you when the follow-up arrives.

e and m

• e.manager_id = m.employee_id reads as a sentence
• A reversed condition is visible the moment it is typed
• The follow-up 'add the manager's hire date' is m.hire_date, no lookup needed
• Shows the interviewer you separated the 2 roles before writing

t1 and t2

• t1.manager_id = t2.employee_id needs the ON clause reread to decode
• A reversed condition looks identical to a correct one
• Every added column starts with 'which one was the manager again?'
• Reads as aliasing done to satisfy the parser

Aliases of 2 or 3 letters are fine when a single letter would be ambiguous: esc and orig say more than t and t2, and they are still short enough to type 10 times without slowing down. The test is whether someone reading the SELECT list alone, without the FROM clause, can tell which role each column came from.

Narrating the FROM clause

Say the aliases as you type them. 'I am reading employees twice: e is the employee, m is the manager. The join condition will connect e's manager_id to m's employee_id.' That is 2 sentences, and by the time you finish them the FROM clause is on the screen and the interviewer has heard the whole plan. If the ON clause then comes out backwards, the interviewer already knows what you meant, and the fix is a 5-second swap rather than a conversation about whether you understood the question.

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.