Self JOIN in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employee names and their managers' names using a self join.

MySQL

SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = [1].id;

Drag options to blanks, or click blank then click option'

Am

Be

Cemployees

Dmanagers

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong alias like 'e.id' instead of 'm.id'.

Using the table name instead of alias in the ON clause.

The alias m refers to the managers table instance in the self join. We join on e.manager_id = m.id to link employees to their managers.

2fill in blank

medium

Complete the code to find employees who have the same manager.

MySQL

SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.[1] = e2.manager_id WHERE e1.id != e2.id;

Drag options to blanks, or click blank then click option'

Amanager_id

Bdepartment_id

Cname

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on employee IDs instead of manager IDs.

Using the same alias on both sides of the join condition.

We join on e1.manager_id = e2.manager_id to find employees sharing the same manager.

3fill in blank

hard

Fix the error in the self join query to correctly list employees and their managers.

MySQL

SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = [1] WHERE m.id = e.manager_id;

Drag options to blanks, or click blank then click option'

Ae.id

Bm.manager_id

Cm.id

De.manager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee's own ID instead of manager's ID in the join.

Confusing the aliases in the ON clause.

The join condition should be e.manager_id = m.id to link employees to their managers correctly.

4fill in blank

hard

Fill both blanks to select employees and their managers' names, including employees without managers.

MySQL

SELECT e.name AS employee, [1].name AS manager FROM employees e LEFT JOIN employees [2] ON e.manager_id = [2].id;

Drag options to blanks, or click blank then click option'

Am

Be

Dmanagers

Attempts:

3 left

💡 Hint

Common Mistakes

Using different aliases in SELECT and JOIN.

Using INNER JOIN instead of LEFT JOIN, missing employees without managers.

The alias m is used for the managers table instance. We use LEFT JOIN to include employees without managers.

5fill in blank

hard

Fill all three blanks to find employees who are also managers of others.

MySQL

SELECT DISTINCT [1].name FROM employees [2] JOIN employees [3] ON [2].id = [3].manager_id;

Drag options to blanks, or click blank then click option'

Am

Be

Dmanagers

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up aliases for employees and managers.

Not using DISTINCT, causing duplicate manager names.

Alias m is used for managers and e for employees. We join on m.id = e.manager_id to find managers who have employees.

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