You have two tables: students (id, name) and enrollments (student_id, course). You want to list all students and the courses they are enrolled in, including students with no enrollments. Which SQL query correctly achieves this?
ASELECT students.name, enrollments.course FROM students INNER JOIN enrollments ON students.id = enrollments.student_id;
BSELECT students.name, enrollments.course FROM enrollments LEFT JOIN students ON students.id = enrollments.student_id;
CSELECT students.name, enrollments.course FROM students LEFT JOIN enrollments ON students.id = enrollments.student_id;
DSELECT students.name, enrollments.course FROM students RIGHT JOIN enrollments ON students.id = enrollments.student_id;
Step-by-Step Solution
Solution:
Step 1: Identify requirement for all students
We want all students listed, even those without enrollments, so the join must keep all rows from students.
Step 2: Choose correct join type
LEFT JOIN keeps all rows from the left table (students) and matches enrollments if any. INNER JOIN excludes students without enrollments. RIGHT JOIN would keep all enrollments, not students.
Final Answer:
SELECT students.name, enrollments.course FROM students LEFT JOIN enrollments ON students.id = enrollments.student_id; -> Option C
Quick Check:
LEFT JOIN keeps all left table rows (students) [OK]
Quick Trick:Use LEFT JOIN to keep all from first table, even if no match [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes students without courses
Using RIGHT JOIN keeps all enrollments, not students
Swapping table order changes join meaning
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
