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?<br>

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.