0

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.

5

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.

| improve this answer | |
  • Thanks! This clear things up :) – MADi May 17 at 18:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .