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.