Which of the following SQL queries correctly uses a self join to find each employee's manager name from an employees table with columns id, name, and manager_id?
easy📝 Syntax Q12 of 15
SQL - Advanced Joins
Which of the following SQL queries correctly uses a self join to find each employee's manager name from an employees table with columns id, name, and manager_id?
ASELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id;
BSELECT e.name, m.name AS manager_name FROM employees e JOIN managers m ON e.manager_id = m.id;
CSELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id;
DSELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id;
Step-by-Step Solution
Solution:
Step 1: Identify correct table aliases and join condition
We must join the employees table to itself using aliases (e and m) and match e.manager_id = m.id to get the manager's name.
Step 2: Check each option
SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; correctly uses self join with proper aliases and join condition. Options B uses a non-existent table 'managers'. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id; reverses the join condition. SELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id; uses LEFT JOIN but with wrong condition.
Final Answer:
SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; -> Option C
Quick Check:
Correct self join syntax = SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; [OK]
Quick Trick:Match child.manager_id to parent.id in self join [OK]
Common Mistakes:
MISTAKES
Using wrong join condition reversing keys
Joining with a non-existent table
Confusing LEFT JOIN with INNER JOIN in this context
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
