Self join for hierarchical data in SQL - Step-by-Step Execution

Concept Flow - Self join for hierarchical data

Start with table

↓

Join table to itself

↓

Match parent ID to child ID

↓

Select parent and child info

↓

Show hierarchical pairs

↓

Use result for hierarchy display or analysis

We join a table to itself to link each row to its parent row, showing hierarchical relationships.

Execution Sample

SQL

SELECT c.name AS child, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

This query finds each category's parent by joining the table to itself on parent-child IDs.

Execution Table

Step	c.id	c.name	c.parent_id	p.id	p.name	Join Condition (c.parent_id = p.id)	Output (child, parent)
1	1	Electronics	NULL	NULL	NULL	No match (NULL parent_id)	('Electronics', NULL)
2	2	Laptops	1	1	Electronics	1 = 1 (match)	('Laptops', 'Electronics')
3	3	Smartphones	1	1	Electronics	1 = 1 (match)	('Smartphones', 'Electronics')
4	4	Gaming Laptops	2	2	Laptops	2 = 2 (match)	('Gaming Laptops', 'Laptops')
5	5	Ultrabooks	2	2	Laptops	2 = 2 (match)	('Ultrabooks', 'Laptops')
6	6	Android Phones	3	3	Smartphones	3 = 3 (match)	('Android Phones', 'Smartphones')
7	7	iPhones	3	3	Smartphones	3 = 3 (match)	('iPhones', 'Smartphones')
8	End	All rows processed					Query complete

💡 All rows processed; no more child-parent pairs to join.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
c.id	1	2	3	4	5	6	7	End	End
c.name	Electronics	Laptops	Smartphones	Gaming Laptops	Ultrabooks	Android Phones	iPhones	End	End
c.parent_id	NULL	1	1	2	2	3	3	End	End
p.id	NULL	1	1	2	2	3	3	End	End
p.name	NULL	Electronics	Electronics	Laptops	Laptops	Smartphones	Smartphones	End	End

Key Moments - 3 Insights

Why does the first row have NULL for the parent columns?

Because the root category has no parent (parent_id is NULL), so no matching row in the join table is found, as shown in execution_table step 1.

How does the join condition link child and parent rows?

The join matches c.parent_id to p.id, so each child's parent_id points to the parent's id, as seen in steps 2-7 where the condition is true.

What happens if a category has no parent in the table?

The LEFT JOIN keeps the child row but shows NULL for parent columns, indicating no parent, as in step 1 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the parent name for 'Gaming Laptops' at step 4?

A Electronics

B Smartphones

C Laptops

D NULL

💡 Hint

Check the 'p.name' column at step 4 in the execution_table.

At which step does the join condition fail due to NULL parent_id?

A Step 3

B Step 1

C Step 5

D Step 7

💡 Hint

Look for where 'c.parent_id' is NULL and no matching 'p.id' exists in the execution_table.

If 'Ultrabooks' had parent_id = 3 instead of 2, what would be its parent name in the output?

A Smartphones

B Electronics

C Laptops

D NULL

💡 Hint

Changing 'c.parent_id' changes which 'p.id' it matches; check execution_table logic for parent lookup.

Concept Snapshot

Self join links a table to itself to show hierarchy.
Syntax: SELECT child, parent FROM table c LEFT JOIN table p ON c.parent_id = p.id;
Parent rows match child rows by ID.
NULL parent_id means no parent (root).
Useful for categories, org charts, etc.

Full Transcript

This visual execution shows how a self join works for hierarchical data. We start with a table of categories where each row may have a parent_id pointing to another row's id. The query joins the table to itself, matching child rows to their parent rows by comparing c.parent_id to p.id. Each step processes one row from the child alias 'c' and finds the matching parent in alias 'p'. If no parent exists (parent_id is NULL), the parent columns are NULL. The output lists child and parent names, showing the hierarchy. This method helps display or analyze hierarchical relationships in one table by linking rows to their parents.