Self join patterns in PostgreSQL - Step-by-Step Execution

Concept Flow - Self join patterns

Start with one table

↓

Create two aliases of the same table

↓

Join aliases on a condition

↓

Select columns from both aliases

↓

Return combined rows

↓

End

A self join uses two aliases of the same table to compare rows within it by joining on a condition.

Execution Sample

PostgreSQL

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	Check first e1 row (Alice, manager_id=2) against all e2 rows	Alice	2	1	Bob	2=1? No
2	Check Alice against e2 row (2, Carol)	Alice	2	2	Carol	2=2? Yes	(Alice, Carol)
3	Check Alice against e2 row (3, Dave)	Alice	2	3	Dave	2=3? No
4	Check second e1 row (Bob, manager_id=3) against all e2 rows	Bob	3	1	Bob	3=1? No
5	Check Bob against e2 row (2, Carol)	Bob	3	2	Carol	3=2? No
6	Check Bob against e2 row (3, Dave)	Bob	3	3	Dave	3=3? Yes	(Bob, Dave)
7	Check third e1 row (Carol, manager_id=NULL) against all e2 rows	Carol	NULL	1	Bob	NULL=1? No
8	Check Carol against e2 row (2, Carol)	Carol	NULL	2	Carol	NULL=2? No
9	Check Carol against e2 row (3, Dave)	Carol	NULL	3	Dave	NULL=3? No
10	No more e1 rows to check						End

💡 All rows from e1 checked against e2; join condition false or true determined output rows.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
e1.name	Alice	Alice	Bob	Bob	Carol
e1.manager_id	2	2	3	3	NULL
e2.id	1	2	3	3	3
e2.name	Bob	Carol	Dave	Dave	Dave

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 to itself, aliases let us treat each copy separately. See execution_table rows where e1 and e2 columns are compared.

What happens if the join condition uses NULL values?

NULL compared to any value is never true, so those rows don't join. See execution_table rows 7-9 where e1.manager_id is NULL and no output row is produced.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output row when e1.name is 'Bob' and e2.name is 'Dave'?

A (Bob, Dave)

B (Dave, Bob)

C (Bob, Carol)

D (Carol, Dave)

💡 Hint

Check execution_table row 6 where join condition is true for Bob and Dave.

At which step does the join condition first evaluate to true?

A Step 1

B Step 4

C Step 2

D Step 6

💡 Hint

Look at execution_table row 2 where Alice joins with Carol.

If e1.manager_id for Carol was 1 instead of NULL, what output row would appear?

A (Carol, Carol)

B (Carol, Bob)

C (Carol, Dave)

D No new output row

💡 Hint

Refer to variable_tracker and execution_table rows 7-9 for NULL behavior and matching ids.

Concept Snapshot

Self join uses the same table twice with aliases.
Join condition compares rows within the table.
Syntax: FROM table AS e1 JOIN table AS e2 ON condition.
Useful for hierarchical or related data in one table.
NULLs do not match in join conditions.
Output combines columns from both aliases.

Full Transcript

A self join means joining a table to itself using two different names called aliases. We compare rows inside the same table by joining on a condition. For example, to find employees and their managers in one employees table, we join employees as e1 to employees as e2 where e1.manager_id equals e2.id. We check each row of e1 against all rows of e2. If the join condition is true, we output a combined row. If the join condition involves NULL, it never matches, so no output row is produced. Aliases are necessary to distinguish the two copies of the same table. This pattern helps find relationships inside one table, like hierarchies or pairs.