Self join concept in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple self join query

Consider a table Employees with columns id, name, and manager_id (which refers to the id of the employee's manager). What is the output of the following SQL query?

SELECT e.name AS Employee, m.name AS Manager
FROM Employees e
JOIN Employees m ON e.manager_id = m.id
ORDER BY e.id;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(20), manager_id INT);
INSERT INTO Employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

A [{"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Alice"}, {"Employee": "David", "Manager": "Bob"}]

B [{"Employee": "Alice", "Manager": "Alice"}, {"Employee": "Bob", "Manager": "Bob"}, {"Employee": "Charlie", "Manager": "Charlie"}, {"Employee": "David", "Manager": "David"}]

C [{"Employee": "Alice", "Manager": null}, {"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Alice"}, {"Employee": "David", "Manager": "Bob"}]

D []

💡 Hint

Think about how the join matches employees to their managers using the manager_id and id columns.

The query joins the Employees table to itself to find each employee's manager by matching e.manager_id to m.id. Alice has no manager (NULL), so she is excluded from the join results. Bob and Charlie have Alice as manager, David has Bob.

🧠 Conceptual

intermediate

Understanding self join purpose

Why do we use a self join in SQL?

A To delete duplicate rows from a table.

B To combine rows from two different tables based on a related column.

C To compare rows within the same table by joining it to itself.

D To create a backup copy of a table.

💡 Hint

Think about when you want to relate rows inside the same table.

A self join is used to join a table to itself to compare or relate rows within that same table, such as finding managers of employees in the same Employees table.

📝 Syntax

advanced

Identify the syntax error in self join query

Which option contains a syntax error in this self join SQL query?

SELECT e.name, m.name
FROM Employees e
JOIN Employees m ON e.manager_id = m.id;

A SELECT e.name, m.name FROM Employees e JOIN Employees m ON e.manager_id = m.id;

B SELECT e.name, m.name FROM Employees e JOIN Employees m WHERE e.manager_id = m.id;

C SELECT e.name, m.name FROM Employees e INNER JOIN Employees m ON e.manager_id = m.id;

D SELECT e.name, m.name FROM Employees e JOIN Employees m ON e.manager_id = m.id ORDER BY e.name;

💡 Hint

Check the JOIN clause syntax carefully.

Option B uses WHERE instead of ON for the join condition, which is a syntax error in JOIN syntax.

❓ query_result

advanced

Output of self join with NULL manager_id

Given the Employees table below, what will this query return?

SELECT e.name AS Employee, COALESCE(m.name, 'No Manager') AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.id
ORDER BY e.id;

Table data:

id | name    | manager_id
1  | Alice   | NULL
2  | Bob     | 1
3  | Charlie | 1
4  | David   | 2

A [{"Employee": "Alice", "Manager": "No Manager"}, {"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Alice"}, {"Employee": "David", "Manager": "Bob"}]

B [{"Employee": "Alice", "Manager": null}, {"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Alice"}, {"Employee": "David", "Manager": "Bob"}]

C [{"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Alice"}, {"Employee": "David", "Manager": "Bob"}]

D []

💡 Hint

LEFT JOIN keeps all employees even if they have no manager.

LEFT JOIN returns all employees. For Alice, manager_id is NULL, so m.name is NULL and COALESCE replaces it with 'No Manager'. Others show their managers.

❓ optimization

expert

Optimizing a self join query for large tables

You have a large Employees table with millions of rows. You want to find each employee's manager name using a self join. Which option is the best way to optimize this query?

SELECT e.name AS Employee, m.name AS Manager
FROM Employees e
JOIN Employees m ON e.manager_id = m.id;

A Use SELECT * instead of selecting specific columns.

B Rewrite the query using a subquery instead of a join.

C Add a WHERE clause filtering employees with manager_id IS NOT NULL.

D Create an index on Employees.id and Employees.manager_id columns before running the query.

💡 Hint

Indexes help speed up join operations on large tables.

Creating indexes on the join columns (id and manager_id) allows the database to quickly find matching rows, greatly improving performance on large tables.

Practice

1. What is the main purpose of a self join in SQL?

easy

A. To combine rows from two tables without any condition

B. To join two different tables based on a common column

C. To join a table to itself to compare rows within the same table

D. To delete duplicate rows from a table

Solution

1. Step 1: Understand the concept of self join

   A self join is used when you want to compare rows within the same table by treating it as two separate tables using aliases.

2. Step 2: Identify the correct purpose

   Joining a table to itself allows you to find relationships or comparisons between rows in the same table, such as hierarchical data or pairs.

3. Final Answer:

   To join a table to itself to compare rows within the same table -> Option C

4. Quick Check:

   Self join = join table to itself [OK]

Hint: Self join means joining table to itself using aliases [OK]

Common Mistakes:

• Confusing self join with joining two different tables
• Thinking self join deletes duplicates
• Assuming self join combines rows without condition

2. Which of the following is the correct syntax to perform a self join on a table named employees with alias e1 and e2?

easy

A. SELECT * FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id;

B. SELECT * FROM employees JOIN employees ON id = manager_id;

C. SELECT * FROM employees e1, employees e2 ON e1.id = e2.manager_id;

D. SELECT * FROM employees e1 INNER JOIN employees e2 ON e1.id = e2.id;

Solution

1. Step 1: Use table aliases for self join

   To join a table to itself, you must use aliases like e1 and e2 to distinguish the two instances.

2. Step 2: Write the join condition correctly

   The join condition should relate columns from the two aliases, for example e1.id = e2.manager_id to find employees and their managers.

3. Final Answer:

   SELECT * FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; -> Option A

4. Quick Check:

   Self join syntax = table alias + join condition [OK]

Hint: Always use aliases to distinguish the same table twice [OK]

Common Mistakes:

• Not using aliases causes syntax errors
• Joining on wrong columns returns wrong results
• Using comma join without aliases is confusing

3. Given the table employees with columns id, name, and manager_id, what will this query return?

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

medium

A. Syntax error due to missing WHERE clause

B. List of employees with their managers' names, NULL if no manager

C. List of employees without managers only

D. List of managers with their employees' names

Solution

1. Step 1: Understand the LEFT JOIN on self join

   The query joins the employees table to itself using aliases e1 and e2, matching e1.manager_id to e2.id to find each employee's manager.

2. Step 2: Interpret the SELECT columns and join type

   Using LEFT JOIN means all employees (e1) appear, even if they have no manager (e2.name will be NULL). The SELECT shows employee and manager names.

3. Final Answer:

   List of employees with their managers' names, NULL if no manager -> Option B

4. Quick Check:

   LEFT JOIN self join shows all employees with managers [OK]

Hint: LEFT JOIN keeps all employees, shows NULL for missing managers [OK]

Common Mistakes:

• Confusing employee and manager columns
• Thinking it lists only managers or only employees without managers
• Assuming syntax error without WHERE clause

4. Identify the error in this self join query:

SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.id;

medium

A. Missing table aliases for employees

B. Using JOIN instead of LEFT JOIN causes error

C. Syntax error due to missing WHERE clause

D. The join condition compares the same column, causing incorrect results

Solution

1. Step 1: Analyze the join condition

   The query joins employees to itself on e1.id = e2.id, which matches each row to itself only, not to related rows.

2. Step 2: Understand the effect of the condition

   This join condition does not find relationships like manager or pairs; it just duplicates rows, which is likely incorrect for self join use.

3. Final Answer:

   The join condition compares the same column, causing incorrect results -> Option D

4. Quick Check:

   Self join needs meaningful join condition, not same column equals [OK]

Hint: Join condition must relate different columns for meaningful self join [OK]

Common Mistakes:

• Joining on identical columns returns only same rows
• Forgetting to use aliases
• Assuming JOIN requires WHERE clause

5. You have a table employees with columns id, name, and manager_id. Write a query using self join to find all employees who share the same manager. Which query correctly achieves this?

hard

A. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id WHERE e1.id <> e2.id;

B. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id;

C. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id WHERE e1.id = e2.id;

D. SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id WHERE e1.id = e2.id;

Solution

1. Step 1: Understand the goal

   We want pairs of employees who share the same manager, so their manager_id values must be equal but employees must be different.

2. Step 2: Write the self join condition

   Joining on e1.manager_id = e2.manager_id finds employees with the same manager. Adding WHERE e1.id <> e2.id excludes pairing an employee with themselves.

3. Final Answer:

   SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id WHERE e1.id <> e2.id; -> Option A

4. Quick Check:

   Same manager and different employees = SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.manager_id WHERE e1.id <> e2.id; [OK]

Hint: Join on manager_id and exclude same employee IDs [OK]

Common Mistakes:

• Joining on employee id instead of manager id
• Not excluding same employee pairs
• Using equality on employee IDs causing no pairs