Self join for hierarchical data in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a self join in SQL?

A self join is when a table is joined with itself to compare rows within the same table. It helps to find relationships between rows.

beginner

Why use a self join for hierarchical data?

Because hierarchical data has parent-child relationships stored in the same table, a self join lets us connect each child row to its parent row.

intermediate

Example: What does this SQL do?

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 each employee with their manager's name by joining the employees table to itself using the manager_id to find the manager's record.

beginner

What is the key column used in a self join for hierarchical data?

Usually, a column like 'parent_id' or 'manager_id' links a row to its parent row's 'id'. This column is used to join the table to itself.

intermediate

Can a self join return rows without parents?

Yes, using a LEFT JOIN in a self join will include rows that have no parent (null parent_id), showing them with no matching parent row.

What does a self join do in SQL?

A Deletes duplicate rows

B Joins two different tables

C Joins a table with itself

D Creates a new table

A self join connects a table to itself to compare rows within the same table.

Which column is typically used to join in hierarchical data self joins?

A Foreign key referencing parent row

B Date column

C Random column

D Primary key only

The foreign key column that points to the parent row is used to join the table to itself.

What type of join is best to include rows without parents in a self join?

A INNER JOIN

B RIGHT JOIN

C FULL JOIN

D LEFT JOIN

LEFT JOIN includes all rows from the left table, even if there is no matching parent row.

In a self join for employees and managers, what does the ON clause usually compare?

A Manager ID to Employee ID

B Employee ID to Manager ID

C Employee ID to Employee ID

D Manager ID to Manager ID

The employee's manager_id is matched to the manager's employee id.

What kind of data is best suited for self joins?

A Flat data with no relationships

B Hierarchical data with parent-child links

C Data with no keys

D Random unstructured data

Self joins are ideal for hierarchical data where rows relate to other rows in the same table.

Explain how a self join works to show parent-child relationships in hierarchical data.

Think about how one row points to another row in the same table.

Describe how to write a SQL query using a self join to list employees with their managers.

Use table aliases like e1 and e2 to distinguish roles.

Practice

1. What is the main purpose of using a self join in SQL when working with hierarchical data?

easy

A. To join two different tables based on a common key

B. To connect rows within the same table to show parent-child relationships

C. To combine rows from multiple tables into one result

D. To delete duplicate rows from a table

Solution

1. Step 1: Understand self join concept

   A self join connects rows within the same table, unlike regular joins that connect different tables.

2. Step 2: Apply to hierarchical data

   Hierarchical data like employees and managers require linking rows to show parent-child links, which self join does.

3. Final Answer:

   To connect rows within the same table to show parent-child relationships -> Option B

4. Quick Check:

   Self join = parent-child links [OK]

Hint: Self join links rows in one table for hierarchy [OK]

Common Mistakes:

• Confusing self join with joining different tables
• Thinking self join deletes duplicates
• Assuming self join merges tables horizontally

2. Which of the following SQL queries correctly uses a self join to find each employee's manager name from an employees table with columns id, name, and manager_id?

easy

A. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id;

B. SELECT e.name, m.name AS manager_name FROM employees e JOIN managers m ON e.manager_id = m.id;

C. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id;

D. SELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id;

Solution

1. Step 1: Identify correct table aliases and join condition

   We must join the employees table to itself using aliases (e and m) and match e.manager_id = m.id to get the manager's name.

2. Step 2: Check each option

   SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; correctly uses self join with proper aliases and join condition. Options B uses a non-existent table 'managers'. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id; reverses the join condition. SELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id; uses LEFT JOIN but with wrong condition.

3. Final Answer:

   SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; -> Option C

4. Quick Check:

   Correct self join syntax = SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; [OK]

Hint: Match child.manager_id to parent.id in self join [OK]

Common Mistakes:

• Using wrong join condition reversing keys
• Joining with a non-existent table
• Confusing LEFT JOIN with INNER JOIN in this context

3. Given the categories table:

id | name       | parent_id
---+------------+----------
1  | Electronics| NULL
2  | Computers  | 1
3  | Laptops    | 2
4  | Phones     | 1
5  | Smartphones| 4

What will be the output of this query?

SELECT c.name AS category, p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id
ORDER BY c.id;

medium

A. [{"category": "Electronics", "parent_category": null}, {"category": "Computers", "parent_category": "Electronics"}, {"category": "Laptops", "parent_category": "Computers"}, {"category": "Phones", "parent_category": "Electronics"}, {"category": "Smartphones", "parent_category": "Phones"}]

B. [{"category": "Electronics", "parent_category": "Electronics"}, {"category": "Computers", "parent_category": "Computers"}, {"category": "Laptops", "parent_category": "Laptops"}, {"category": "Phones", "parent_category": "Phones"}, {"category": "Smartphones", "parent_category": "Smartphones"}]

C. [{"category": "Electronics", "parent_category": "Computers"}, {"category": "Computers", "parent_category": "Laptops"}, {"category": "Laptops", "parent_category": "Phones"}, {"category": "Phones", "parent_category": "Smartphones"}, {"category": "Smartphones", "parent_category": null}]

D. [{"category": "Electronics", "parent_category": "Phones"}, {"category": "Computers", "parent_category": "Smartphones"}, {"category": "Laptops", "parent_category": null}, {"category": "Phones", "parent_category": "Computers"}, {"category": "Smartphones", "parent_category": "Electronics"}]

Solution

1. Step 1: Understand the LEFT JOIN on self

   The query joins each category (c) with its parent category (p) by matching c.parent_id = p.id. If no parent, parent_category is NULL.

2. Step 2: Map each category to its parent

   Electronics has NULL parent, Computers' parent is Electronics, Laptops' parent is Computers, Phones' parent is Electronics, Smartphones' parent is Phones.

3. Final Answer:

   [{"category": "Electronics", "parent_category": null}, {"category": "Computers", "parent_category": "Electronics"}, {"category": "Laptops", "parent_category": "Computers"}, {"category": "Phones", "parent_category": "Electronics"}, {"category": "Smartphones", "parent_category": "Phones"}] -> Option A

4. Quick Check:

   Parent matches child.parent_id = parent.id [OK]

Hint: Parent name is NULL if parent_id is NULL [OK]

Common Mistakes:

• Assuming parent_category equals category name
• Mixing up parent_id and id in join condition
• Ignoring NULL parent_id results

4. Consider this SQL query intended to list employees and their managers:

SELECT e.name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.id = m.manager_id;

What is the error in this query?

medium

A. The join condition is reversed; it should be e.manager_id = m.id

B. The table alias 'm' is not defined

C. The query should use LEFT JOIN instead of JOIN

D. The SELECT clause should use m.manager_name instead of m.name

Solution

1. Step 1: Analyze the join condition

   The query joins on e.id = m.manager_id, which means employee id equals manager's manager_id, which is incorrect.

2. Step 2: Correct join condition for manager lookup

   To find each employee's manager, join on e.manager_id = m.id so employee's manager_id matches manager's id.

3. Final Answer:

   The join condition is reversed; it should be e.manager_id = m.id -> Option A

4. Quick Check:

   Join on employee.manager_id = manager.id [OK]

Hint: Match child.manager_id to parent.id, not reverse [OK]

Common Mistakes:

• Reversing join keys causing wrong matches
• Confusing alias usage
• Assuming INNER JOIN always needed

5. You have a parts table with columns part_id, part_name, and parent_part_id. Write a query to list each part with its top-level ancestor part name (the root parent with parent_part_id IS NULL). Which approach correctly achieves this using self joins?

hard

A. Use UNION ALL to combine parts with their children without join

B. Use a single self join on part_id = parent_part_id to get the immediate parent only

C. Use GROUP BY part_id and MAX(parent_part_id) to find the top ancestor

D. Use multiple self joins chaining parent_part_id until parent_part_id IS NULL, selecting the top ancestor name

Solution

1. Step 1: Understand hierarchical traversal

   Finding the top-level ancestor requires following parent links repeatedly until reaching a part with no parent (parent_part_id IS NULL).

2. Step 2: Use multiple self joins or recursive CTE

   This can be done by chaining self joins or using recursive queries to climb the hierarchy to the root ancestor.

3. Final Answer:

   Use multiple self joins chaining parent_part_id until parent_part_id IS NULL, selecting the top ancestor name -> Option D

4. Quick Check:

   Top ancestor requires repeated self joins [OK]

Hint: Top ancestor needs repeated self joins or recursion [OK]

Common Mistakes:

• Using single join only finds immediate parent
• Trying to use aggregate functions incorrectly
• Ignoring recursive nature of hierarchy