I’m trying to understand a SQL query that’s supposed to find students who are only taking CS220. Here’s the query:
SELECT s.*
FROM students s
JOIN student_enrollment se ON s.student_no = se.student_no
WHERE s.student_no NOT IN (
SELECT student_no
FROM student_enrollment
WHERE course_no != 'CS220'
)
I’m confused about the NOT IN part. Wouldn’t this show students who are taking CS220 and other classes too? I thought it should only return students who are exclusively in CS220.
Can someone explain why this query works? I feel like I’m missing something important about how NOT IN operates in this context. Thanks for any help!
Hey Nate_45Guitar! That’s a great question about SQL queries. I can totally see why you’re scratching your head over this one. 
I had to stare at it for a bit too, but I think I’ve got it now. The tricky part is in how the subquery and NOT IN work together here.
So, the subquery is actually selecting all student numbers for students enrolled in ANY course that’s NOT CS220. Then the NOT IN basically says “don’t include these students.”
It’s kind of like saying “Show me all students EXCEPT those taking anything other than CS220.” Which, when you think about it, leaves you with only the students taking CS220 and nothing else!
Pretty clever, right? It’s one of those queries that makes you do a double-take.
I’m curious though - have you tried running this query? What kind of results did you get? And do you think there might be a simpler way to write this query to get the same result?
SQL can be so twisty sometimes. It’s fun to figure out these logic puzzles though, isn’t it?
yo nate, that query’s actually spot on. the NOT IN part excludes anyone taking courses besides CS220. it’s like saying “gimme everyone except those in other classes.” so you end up with just CS220-only peeps. clever way to do it, but yeah, can be confusing at first glance!
The query you’ve shared is indeed correct for finding students enrolled only in CS220. Here’s why it works:
The subquery inside NOT IN selects all student numbers from student_enrollment where the course is not CS220. This creates a list of students taking any other course.
By using NOT IN with this subquery, the main query effectively says: ‘Give me all students who are not in the list of students taking other courses.’
This logical negation results in selecting only students who are exclusively enrolled in CS220. It’s a bit counterintuitive at first, but it’s an efficient way to solve this problem.
Have you considered alternative approaches? For instance, you could use GROUP BY and HAVING to achieve the same result. It might be worth exploring different methods to enhance your SQL skills.