PSET7 Movies Q8 - Why these queries return different results?

Question

I'm working on pset7 movies problem of the CS50 course and I have a question regarding 8.sql, which asks to write a SQL query to list the names of all people who starred in Toy Story.

To my understanding the following two queries should be equivalent and return the same results, but this query returns 1 row

SELECT name FROM people WHERE id =
(SELECT person_id FROM stars INNER JOIN movies ON stars.movie_id = movies.id 
WHERE movies.title = "Toy Story");

while this returns 4 rows

SELECT name FROM people INNER JOIN stars ON people.id = stars.person_id INNER JOIN movies 
ON stars.movie_id = movies.id WHERE title = "Toy Story";

So obviously the queries aren't equivalent, but I cannot understand why. I would really appreciate if someone could explain the difference of the two queries.

Answer 1

It's because in your first query you said where id = (.... this query here is expecting only 1 value so in the query inside the parentheses the first value it gets is the only value that is checked. To make your query correct all you have to do is change the = with IN, the IN command here checks a list of values. e.g. SELECT * FROM smth WHERE name IN ('DAVID', 'BRIAN', 'COLTON');

this query here checks all the values in the parentheses.