You have two tables: Students and Enrollments. Some students are not enrolled in any course. Which query correctly lists all students and their courses, showing NULL for students without enrollments?
hard📝 Application Q15 of 15
SQL - LEFT and RIGHT JOIN
You have two tables: Students and Enrollments. Some students are not enrolled in any course. Which query correctly lists all students and their courses, showing NULL for students without enrollments?
ASELECT s.name, e.course FROM Students s INNER JOIN Enrollments e ON s.id = e.student_id;
BSELECT s.name, e.course FROM Students s RIGHT OUTER JOIN Enrollments e ON s.id = e.student_id;
CSELECT s.name, e.course FROM Enrollments e LEFT OUTER JOIN Students s ON s.id = e.student_id;
DSELECT s.name, e.course FROM Students s LEFT OUTER JOIN Enrollments e ON s.id = e.student_id;
Step-by-Step Solution
Solution:
Step 1: Identify which table has all students
Students table contains all students, including those without enrollments.
Step 2: Choose join to keep all students
LEFT OUTER JOIN with Students as left table keeps all students, adding course info or NULL if no enrollment.
Step 3: Evaluate other options
INNER JOIN excludes students without enrollments. RIGHT OUTER JOIN with Enrollments left excludes students without enrollments. LEFT OUTER JOIN with Enrollments left table excludes students without enrollments.
Final Answer:
SELECT s.name, e.course FROM Students s LEFT OUTER JOIN Enrollments e ON s.id = e.student_id; -> Option D
Quick Check:
LEFT OUTER JOIN keeps all left table rows [OK]
Quick Trick:LEFT OUTER JOIN with Students on left keeps all students [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes students without courses
Swapping left and right tables in join
Using RIGHT OUTER JOIN incorrectly
Master "LEFT and RIGHT JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
