Self JOIN in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a Self JOIN Query

Given the table Employees with columns id, name, and manager_id (which references id of the manager), what is the output of the following query?

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

MySQL

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

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

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

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

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

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

💡 Hint

Think about how the manager_id links to the id in the same table.

The query joins the Employees table to itself to find each employee's manager name. If manager_id is NULL, the manager name is NULL. Otherwise, it matches the manager's id to get the manager's name.

📝 Syntax

intermediate

Identify the Syntax Error in Self JOIN

Which option contains a syntax error in this self JOIN 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 ON e.manager_id = m.id ORDER BY;

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

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

💡 Hint

Check the ORDER BY clause syntax carefully.

Option B has an incomplete ORDER BY clause without specifying any column, causing a syntax error.

❓ optimization

advanced

Optimizing a Self JOIN Query for Performance

You have a large Employees table with millions of rows. Which option will improve the performance of this self JOIN query the most?

SELECT e.name, m.name
FROM Employees e
JOIN Employees m ON e.manager_id = m.id
WHERE e.department = 'Sales';

A Add an index on Employees(manager_id) and Employees(department).

B Add an index only on Employees(name).

C Add a foreign key constraint on Employees(manager_id) referencing Employees(id).

D Rewrite the query using UNION instead of JOIN.

💡 Hint

Think about which columns are used in JOIN and WHERE clauses.

Indexes on the columns used in JOIN and WHERE conditions speed up lookups and filtering, improving query performance.

🔧 Debug

advanced

Debugging Unexpected NULLs in Self JOIN Result

A query returns NULL for all manager names even though managers exist. Which option explains the most likely cause?

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

A The JOIN should be INNER JOIN instead of LEFT JOIN.

B The query is missing a GROUP BY clause.

C The <code>manager_id</code> values in Employees do not match any <code>id</code> values due to data inconsistency.

D The table alias <code>m</code> is not allowed in self JOIN.

💡 Hint

Check if the join condition matches any rows.

If manager_id values do not correspond to any id in the table, the LEFT JOIN returns NULL for manager columns.

🧠 Conceptual

expert

Understanding Recursive Relationships with Self JOIN

Consider an Employees table where each employee can have a manager, and managers can also have managers, forming a hierarchy. Which SQL approach best retrieves the full management chain for a given employee in MySQL 8+?

A Use a recursive Common Table Expression (CTE) with a self JOIN to traverse the hierarchy.

B Use multiple nested JOINs manually for each level of management.

C Use a simple SELECT with GROUP BY on manager_id.

D Use a UNION ALL without recursion to combine employee and manager rows.

💡 Hint

Think about how to handle an unknown number of management levels.

Recursive CTEs allow querying hierarchical data by repeatedly joining the table to itself until all levels are retrieved.