You have tables students(id, name) and enrollments(student_id, course_id). Write a query to find students not enrolled in any course, considering some students may have NULL IDs. Which query correctly handles this?
ASELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL AND s.id IS NOT NULL;
BSELECT s.name FROM students s RIGHT JOIN enrollments e ON s.id = e.student_id WHERE s.id IS NULL;
CSELECT s.name FROM students s INNER JOIN enrollments e ON s.id = e.student_id WHERE s.id IS NULL;
DSELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL;
Step-by-Step Solution
Solution:
Step 1: Use LEFT JOIN to find unmatched students
LEFT JOIN students with enrollments keeps all students; unmatched enrollments are NULL.
Step 2: Filter students without enrollments and exclude NULL student IDs
WHERE e.student_id IS NULL finds students without courses; adding s.id IS NOT NULL excludes students with NULL IDs to avoid incorrect matches.
Final Answer:
SELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL AND s.id IS NOT NULL; -> Option A
Quick Check:
LEFT JOIN + right NULL + exclude NULL left keys = correct unmatched [OK]
Quick Trick:Exclude NULL keys on left table when filtering unmatched [OK]
Common Mistakes:
MISTAKES
Ignoring NULL IDs in left table
Using INNER JOIN which excludes unmatched rows
Using RIGHT JOIN incorrectly
Master "LEFT and RIGHT JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
