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.

Practice

1. What is the main purpose of using a SELF JOIN in SQL?

easy

A. To combine rows from two tables without any condition

B. To join two different tables based on a common column

C. To join a table to itself to compare rows within the same table

D. To delete duplicate rows from a table

Solution

1. Step 1: Understand what a SELF JOIN does

   A SELF JOIN connects a table to itself using aliases to compare rows within the same table.

2. Step 2: Compare with other JOIN types

   Other joins combine different tables or rows but SELF JOIN specifically works on one table to find relationships inside it.

3. Final Answer:

   To join a table to itself to compare rows within the same table -> Option C

4. Quick Check:

   SELF JOIN = join table to itself [OK]

Hint: SELF JOIN means joining table to itself using aliases [OK]

Common Mistakes:

• Confusing SELF JOIN with JOIN between two different tables
• Thinking SELF JOIN deletes duplicates
• Assuming SELF JOIN combines unrelated tables

2. Which of the following is the correct syntax to perform a SELF JOIN on a table named employees with aliases e1 and e2 to find employees and their managers?

easy

A. SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

B. SELECT * FROM employees JOIN employees ON employees.manager_id = employees.id;

C. SELECT * FROM employees e1 JOIN employees e2 WHERE e1.id = e2.manager_id;

D. SELECT * FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.id;

Solution

1. Step 1: Identify correct alias usage

   Using aliases e1 and e2 for the same table is required for SELF JOIN to distinguish rows.

2. Step 2: Check JOIN condition syntax

   The condition e1.manager_id = e2.id correctly links employees to their managers.

3. Final Answer:

   SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; -> Option A

4. Quick Check:

   Correct alias and ON condition = SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; [OK]

Hint: Use aliases and ON clause for SELF JOIN syntax [OK]

Common Mistakes:

• Not using aliases for the same table
• Missing ON clause or wrong condition
• Using comma join without condition

3. Given the table employees with columns id, name, and manager_id, what will this query return?

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

medium

A. List of employees with their managers' names; NULL if no manager

B. List of managers with their employees' names

C. List of employees with their own names repeated

D. Syntax error due to missing WHERE clause

Solution

1. Step 1: Understand LEFT JOIN with SELF JOIN

   The query joins employees to themselves matching manager_id to id, showing employee and manager names.

2. Step 2: Interpret NULL values

   If an employee has no manager (manager_id is NULL), LEFT JOIN keeps employee row with manager name as NULL.

3. Final Answer:

   List of employees with their managers' names; NULL if no manager -> Option A

4. Quick Check:

   LEFT JOIN keeps all employees, shows manager or NULL [OK]

Hint: LEFT JOIN keeps all employees, shows NULL for missing managers [OK]

Common Mistakes:

• Confusing employee and manager roles
• Expecting only matched rows (INNER JOIN behavior)
• Thinking query has syntax error

4. Identify the error in this SELF JOIN query:

SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id WHERE e1.manager_id = NULL;

medium

A. JOIN condition is reversed

B. Missing alias for employees table

C. Using JOIN instead of LEFT JOIN causes error

D. Incorrect use of NULL comparison in WHERE clause

Solution

1. Step 1: Check WHERE clause for NULL comparison

   Using = NULL is incorrect; SQL requires IS NULL to check NULL values.

2. Step 2: Verify other parts of query

   Aliases are correct, JOIN condition is valid, and JOIN type is allowed.

3. Final Answer:

   Incorrect use of NULL comparison in WHERE clause -> Option D

4. Quick Check:

   Use IS NULL, not = NULL for NULL checks [OK]

Hint: Use IS NULL, not = NULL to check NULL values [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Confusing JOIN condition direction
• Assuming JOIN type causes error

5. You have a table employees with columns id, name, and manager_id. Write a query using SELF JOIN to find all employees who are also managers of others. Which query correctly returns the names of employees who manage at least one other employee?

hard

A. SELECT e1.name FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.id IS NULL;

B. SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id;

C. SELECT e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

D. SELECT e1.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id WHERE e1.id = e2.id;

Solution

1. Step 1: Understand the requirement

   We want employees who appear as managers (their id is manager_id for others).

2. Step 2: Analyze each query

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; joins employees to those they manage and selects distinct manager names correctly. SELECT e1.name FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.id IS NULL; finds employees with no subordinates (wrong). SELECT e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; returns manager names duplicated for each subordinate (wrong). SELECT e1.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id WHERE e1.id = e2.id; has incorrect condition and will return no rows.

3. Final Answer:

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; -> Option B

4. Quick Check:

   Managers have others with manager_id = their id [OK]

Hint: Join on e1.id = e2.manager_id to find managers [OK]

Common Mistakes:

• Selecting employees without subordinates
• Reversing join condition
• Using wrong WHERE clause filters