Self JOIN in MySQL - Step-by-Step Execution

Concept Flow - Self JOIN

Start with a table

↓

Create two aliases of the same table

↓

Join the table to itself using a condition

↓

Match rows based on the join condition

↓

Return combined rows as result

A Self JOIN uses the same table twice with different names to compare rows within that table.

Execution Sample

MySQL

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

This query lists employees with their managers by joining the employees table to itself.

Execution Table

Step	Action	e1 row	e2 row	Join Condition	Output Row
1	Start with employees table	All rows	All rows	N/A	No output yet
2	Alias employees as e1 and e2	e1: employee 1	e2: employee 1	Check e1.manager_id = e2.id	No match (manager_id null), output (employee 1, NULL)
3	Check e1: employee 2, e2: employee 1	e1: employee 2	e2: employee 1	e1.manager_id=1, e2.id=1	Match found, output (employee 2, employee 1)
4	Check e1: employee 3, e2: employee 2	e1: employee 3	e2: employee 2	e1.manager_id=2, e2.id=2	Match found, output (employee 3, employee 2)
5	Check e1: employee 4, e2: employee 3	e1: employee 4	e2: employee 3	e1.manager_id=3, e2.id=3	Match found, output (employee 4, employee 3)
6	Check e1: employee 5, e2: employee 4	e1: employee 5	e2: employee 4	e1.manager_id=4, e2.id=4	Match found, output (employee 5, employee 4)
7	No more rows in e1	N/A	N/A	N/A	End of join

💡 All rows in e1 checked against e2, join complete

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
e1 current row	None	employee 1	employee 2	employee 3	employee 4	employee 5	None
e2 current row	None	employee 1	employee 1	employee 2	employee 3	employee 4	None
Output rows	Empty	(employee 1, NULL)	(employee 1, NULL), (employee 2, employee 1)	(employee 1, NULL), (employee 2, employee 1), (employee 3, employee 2)	(employee 1, NULL), (employee 2, employee 1), (employee 3, employee 2), (employee 4, employee 3)	(employee 1, NULL), (employee 2, employee 1), (employee 3, employee 2), (employee 4, employee 3), (employee 5, employee 4)	All employee-manager pairs (NULL for no manager)

Key Moments - 2 Insights

Why do we need to use aliases like e1 and e2 in a Self JOIN?

Because we join the same table twice, aliases let us treat each copy separately. Without aliases, the database can't tell which instance of the table we mean. See execution_table steps 2 and 3 where e1 and e2 represent different rows.

What happens if an employee has no manager (manager_id is NULL)?

The join condition e1.manager_id = e2.id fails, so no matching row from e2 is found. In a LEFT JOIN, the employee still appears with NULL for manager. See execution_table step 2 where employee 1 has no manager.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the first output row produced?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Check the Output Row column to see when the first output row appears.

According to variable_tracker, what is the e1 current row after Step 4?

A employee 4

B employee 2

C employee 3

D employee 1

💡 Hint

Look at the e1 current row values under After Step 4.

If we changed the join to INNER JOIN, what would happen to employees with no manager?

A They would be excluded from the output

B They would still appear with NULL manager

C They would appear twice

D The query would error

💡 Hint

Recall LEFT JOIN keeps unmatched rows, INNER JOIN does not.

Concept Snapshot

Self JOIN lets you join a table to itself by using aliases.
Syntax: SELECT ... FROM table AS t1 JOIN table AS t2 ON condition.
Use aliases to distinguish the two copies.
Useful to compare rows within the same table.
LEFT JOIN keeps unmatched rows; INNER JOIN excludes them.

Full Transcript

A Self JOIN is when you join a table to itself using different names called aliases. This helps compare rows inside the same table. For example, to find employees and their managers in one employees table, you join the table to itself matching employee.manager_id to manager.id. You must use aliases like e1 and e2 to tell the database which copy you mean. The join checks each row in e1 against rows in e2. If the manager_id matches an id in e2, the pair is output. If no match and using LEFT JOIN, the employee still appears with NULL for manager. This process repeats for all rows. The execution table shows each step checking rows and producing output. The variable tracker shows how current rows and output change step by step. Key points are why aliases are needed and what happens when no manager exists. The quiz tests understanding of when output appears, variable states, and join type effects.