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.

Practice

1. What is the main purpose of using 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 what a SELF JOIN does

   A SELF JOIN connects a table to itself using aliases to compare rows within the same table.

2. Step 2: Compare with other JOIN types

   Other joins combine different tables or rows but SELF JOIN specifically works on one table to find relationships inside it.

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 JOIN between two different tables
• Thinking SELF JOIN deletes duplicates
• Assuming SELF JOIN combines unrelated tables

2. Which of the following is the correct syntax to perform a SELF JOIN on a table named employees with aliases e1 and e2 to find employees and their managers?

easy

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

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

C. SELECT * FROM employees e1 JOIN employees e2 WHERE e1.id = e2.manager_id;

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

Solution

1. Step 1: Identify correct alias usage

   Using aliases e1 and e2 for the same table is required for SELF JOIN to distinguish rows.

2. Step 2: Check JOIN condition syntax

   The condition e1.manager_id = e2.id correctly links employees to their managers.

3. Final Answer:

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

4. Quick Check:

   Correct alias and ON condition = SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; [OK]

Hint: Use aliases and ON clause for SELF JOIN syntax [OK]

Common Mistakes:

• Not using aliases for the same table
• Missing ON clause or wrong condition
• Using comma join without condition

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. List of employees with their managers' names; NULL if no manager

B. List of managers with their employees' names

C. List of employees with their own names repeated

D. Syntax error due to missing WHERE clause

Solution

1. Step 1: Understand LEFT JOIN with SELF JOIN

   The query joins employees to themselves matching manager_id to id, showing employee and manager names.

2. Step 2: Interpret NULL values

   If an employee has no manager (manager_id is NULL), LEFT JOIN keeps employee row with manager name as NULL.

3. Final Answer:

   List of employees with their managers' names; NULL if no manager -> Option A

4. Quick Check:

   LEFT JOIN keeps all employees, shows manager or NULL [OK]

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

Common Mistakes:

• Confusing employee and manager roles
• Expecting only matched rows (INNER JOIN behavior)
• Thinking query has syntax error

4. Identify the error in this SELF JOIN query:

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

medium

A. JOIN condition is reversed

B. Missing alias for employees table

C. Using JOIN instead of LEFT JOIN causes error

D. Incorrect use of NULL comparison in WHERE clause

Solution

1. Step 1: Check WHERE clause for NULL comparison

   Using = NULL is incorrect; SQL requires IS NULL to check NULL values.

2. Step 2: Verify other parts of query

   Aliases are correct, JOIN condition is valid, and JOIN type is allowed.

3. Final Answer:

   Incorrect use of NULL comparison in WHERE clause -> Option D

4. Quick Check:

   Use IS NULL, not = NULL for NULL checks [OK]

Hint: Use IS NULL, not = NULL to check NULL values [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Confusing JOIN condition direction
• Assuming JOIN type causes error

5. You have a table employees with columns id, name, and manager_id. Write a query using SELF JOIN to find all employees who are also managers of others. Which query correctly returns the names of employees who manage at least one other employee?

hard

A. SELECT e1.name FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.id IS NULL;

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

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

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

Solution

1. Step 1: Understand the requirement

   We want employees who appear as managers (their id is manager_id for others).

2. Step 2: Analyze each query

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; joins employees to those they manage and selects distinct manager names correctly. SELECT e1.name FROM employees e1 LEFT JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.id IS NULL; finds employees with no subordinates (wrong). SELECT e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; returns manager names duplicated for each subordinate (wrong). SELECT e1.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id WHERE e1.id = e2.id; has incorrect condition and will return no rows.

3. Final Answer:

   SELECT DISTINCT e1.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id; -> Option B

4. Quick Check:

   Managers have others with manager_id = their id [OK]

Hint: Join on e1.id = e2.manager_id to find managers [OK]

Common Mistakes:

• Selecting employees without subordinates
• Reversing join condition
• Using wrong WHERE clause filters