Self join concept in SQL - Step-by-Step Execution

Concept Flow - Self join concept

Start with one table

↓

Create two aliases of the same table

↓

Join the table aliases on a related column

↓

Filter or select matching rows

↓

Return combined rows showing relationships within the same table

A self join uses one table twice by giving it two names (aliases) and joining it to itself to find related rows.

Execution Sample

SQL

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

This query finds each employee and their manager by joining the employees table to itself.

Execution Table

Step	Action	e1.name	e1.manager_id	e2.id	e2.name	Join Condition (e1.manager_id = e2.id)	Output Row
1	Start with first employee row	Alice	2	2	Bob	2 = 2 (True)	Alice, Bob
2	Next employee row	Charlie	3	2	Bob	3 = 2 (False)
3	Check next manager row	Charlie	3	3	David	3 = 3 (True)	Charlie, David
4	Next employee row	Eve	2	2	Bob	2 = 2 (True)	Eve, Bob
5	Next employee row	Frank	4	2	Bob	4 = 2 (False)
6	Check next manager row	Frank	4	3	David	4 = 3 (False)
7	Check next manager row	Frank	4	4	Grace	4 = 4 (True)	Frank, Grace
8	No more rows						End of join

💡 All employee rows checked against all manager rows; join condition false or true determines output.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
e1.name	Alice	Charlie	Charlie	Eve	Frank	Frank	Frank	Frank
e1.manager_id	2	3	3	2	4	4	4	4
e2.id	2	2	3	2	2	3	4	4
e2.name	Bob	Bob	David	Bob	Bob	David	Grace	Grace

Key Moments - 3 Insights

Why do we need to use aliases like e1 and e2 in a self join?

Because we use the same table twice in the query, aliases help SQL know which copy we mean. See execution_table rows where e1 and e2 columns differ.

How does the join condition work when joining a table to itself?

The join condition compares columns from the two aliases. For example, e1.manager_id = e2.id matches employees to their managers, as shown in the join condition column in execution_table.

What happens if the join condition is false for some rows?

Those rows do not appear in the output. For example, in execution_table rows 2, 5, and 6 the condition is false, so no output row is produced.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output row at step 3?

A Charlie, David

B Alice, Bob

C Eve, Bob

D Frank, Grace

💡 Hint

Check the Output Row column in execution_table at step 3.

At which step does the join condition first become false?

A Step 5

B Step 2

C Step 4

D Step 7

💡 Hint

Look at the Join Condition column in execution_table for the first 'False' value.

If we changed the join condition to e1.id = e2.manager_id, what would happen?

A It would find employees with no managers.

B It would cause a syntax error.

C It would find managers and their employees instead.

D It would return the same results.

💡 Hint

Think about reversing the join condition and how it changes the relationship direction.

Concept Snapshot

Self join uses one table twice by giving it two aliases.
Join the aliases on related columns within the same table.
Useful to find relationships like employee-manager in one table.
Syntax: FROM table AS t1 JOIN table AS t2 ON t1.col = t2.col
Output combines rows from the same table based on the join condition.

Full Transcript

A self join means joining a table to itself. We do this by giving the table two different names called aliases. Then we join these aliases on columns that relate rows inside the same table. For example, to find each employee's manager, we join the employees table to itself where the employee's manager_id matches the manager's id. The execution table shows step-by-step how each employee row is matched with manager rows. When the join condition is true, the pair appears in the output. When false, it does not. Aliases are important to tell SQL which copy of the table we mean. Changing the join condition changes the relationship direction we find. This concept helps find relationships inside one table using SQL.