SQL nested WERE

I’m a beginner in SQL and I don’t understand the nested WHERE request:

When I first request:

SELECT movies.title, people.name
FROM stars
INNER JOIN movies ON movies.id = stars.movie_id
INNER JOIN people ON people.id = stars.person_id
INNER JOIN ratings ON ratings.movie_id = stars.movie_id


WHERE people.name = 'Helena'

The return is correct (all film with Helena).

Then I’ve tried some useless nested in order to understand how nested works:

SELECT movies.title, people.name
FROM stars
INNER JOIN movies ON movies.id = stars.movie_id
INNER JOIN people ON people.id = stars.person_id
INNER JOIN ratings ON ratings.movie_id = stars.movie_id
WHERE  people.name = 'Helena' IN (

SELECT movies.title
FROM stars
WHERE people.name = 'Helena' )

Return me 0.

What I thought:

1 retrieve data in the parenthesis (check for ‘Helena’ in movies)

2 retrieve data by checking the result of the parenthesis (‘Helena’ in the result 1)

Someone could explain to me the principle of the nested SQL with this request?

Thanks in advance,

Answer

Your code makes no sense. That is the issue.

This is how it gets parsed:

WHERE (people.name = 'Helena') IN (SELECT movies.title
                                   FROM stars
                                   WHERE people.name = 'Helena'
                                  )

The first part is a boolean expression that evaluates to 0, 1, or NULL. So, if it is true, this looks like:

WHERE 1 IN (SELECT movies.title
            FROM stars
            WHERE people.name = 'Helena'
           )

And “1” seems highly unlikely as a movie name.

The subquery makes even less sense. You are selecting from stars, but do not reference any columns from that table. This just creates a large list of titles from the outer reference — or none at all if the where clause is not true.

Given that this query makes no sense, you might want to ask a NEW query, provide sample data, desired results, and an explanation of what you want to accomplish.