[SQL]1対多対1の関係のテーブルから、２条件でAND検索する方法

Question

前提

現在cs50という授業で、SQLの勉強をしています。
課題の一つがわからず、ネット上の解答を見て解決はしたものの、なぜ解決したのかが理解できなかったため質問いたします。

実現したいこと

映画に関する以下の形式のDBから、Johnny DeppとHelena Bonham Carterが共演した作品のmovies.idを検索する。

CREATE TABLE movies (
                   id INTEGER,
                   title TEXT NOT NULL,
                   year NUMERIC,
                   PRIMARY KEY(id)
               );
CREATE TABLE stars (
               movie_id INTEGER NOT NULL,
               person_id INTEGER NOT NULL,
               FOREIGN KEY(movie_id) REFERENCES movies(id),
               FOREIGN KEY(person_id) REFERENCES people(id)
           );
CREATE TABLE people (
               id INTEGER,
               name TEXT NOT NULL,
               birth NUMERIC,
               PRIMARY KEY(id)
           );

• [ ]

発生している問題・エラーメッセージ

検索結果が0件になる

該当のソースコード

SELECT movies.id, title FROM movies
    JOIN stars ON movies.id = stars.movie_id
    JOIN people ON stars.person_id = people.id
    WHERE people.name = "Johnny Depp"
    AND movies.id IN
        (SELECT movies.id FROM movies
        WHERE name = "Helena Bonham Carter");

試したこと

• 以下のコード(サブクエリ内で再度JOINを行ったもの)では目的の検索結果が得られた。

SELECT movies.id, title FROM movies
    JOIN stars ON movies.id = stars.movie_id
    JOIN people ON stars.person_id = people.id
    WHERE people.name = "Johnny Depp"
    AND movies.id IN
        (SELECT movies.id FROM movies
        JOIN stars ON movies.id = stars.movie_id
        JOIN people ON stars.person_id = people.id
        WHERE name = "Helena Bonham Carter");

• 私のコードでも、ANDで繋いだいずれかの条件のみで検索すれば(= 一人ずつ検索すれば)、正しい答えが返ってくることは確認済み
• そもそも一つのmovies.idに対応するレコードは複数存在する(一つの映画には複数人出演しているため)が、私の検索方法では一つのmovies.idに対しレコードは一つしか返ってこなかった。従って私のコードのAND以下の検索では、movies.idの数字だけでなくそのレコード自体を抽出しているため、二つの条件の積集合が存在しないと考えた。しかしそれが上記のコードにより解決する理屈がわからない。

Answer 1

最初のSQLのサブクエリ内の下記のSQLは、name は people.name ということになります が、movies のフィールド以外を対象にしたら抽出条件として意味をなさないので、結果は0件になります。

SELECT movies.id FROM movies
        WHERE name = "Helena Bonham Carter"

よって、people.name = "Helena Bonham Carter" という条件式になりますので、親クエリのフィールドに対する条件になります。
つまり、親クエリに people.name = "Johnny Depp" and people.name = "Helena Bonham Carter"という条件を設定したのと同意になります。
このような条件に合致するレコードはないので、当然1件を抽出されないことになります。

二つ目のSQLは、サブクエリ内に、nameフィールドはあります。この場合、近いnameフィールドを参照しますので、サブクエリ内のpeopleテーブルのnameフィールドを参照します。つまり、サブクエリに対する条件ということになります。親クエリには影響を与えません。よって、希望の結果が得らることになります。

---

２つ目のSQLは、メインとサブのpeopleテーブルに別名をつけて記述すると下記になります。

SELECT movies.id, title FROM movies
    JOIN stars ON movies.id = stars.movie_id
    JOIN people a ON stars.person_id = a.id
    WHERE a.name = "Johnny Depp"
    AND movies.id IN
        (SELECT movies.id FROM movies
        JOIN stars ON movies.id = stars.movie_id
        JOIN people b ON stars.person_id = b.id
        WHERE b.name = "Helena Bonham Carter");

これは希望の結果が得られます。
このサブクエリの条件を下記のように親クエリのフィールドを参照するように書き換えると、結果は0件になります。

SELECT movies.id, title FROM movies
    JOIN stars ON movies.id = stars.movie_id
    JOIN people a ON stars.person_id = a.id
    WHERE a.name = "Johnny Depp"
    AND movies.id IN
        (SELECT movies.id FROM movies
        JOIN stars ON movies.id = stars.movie_id
        JOIN people b ON stars.person_id = b.id
        WHERE a.name = "Helena Bonham Carter");

Comments

[gomashabu]

ご回答ありがとうございます。
上の方でJOINをしても、サブクエリ内に入ると再びJOINをする必要があるということでしょうか？
しかし以下のコードでは、再度JOINしなくてもpeople.name = "Johnny Depp"を満たすものが全件返ってくるのですが、これはなぜなのかお聞きできますでしょうか。

```SQL
SELECT movies.id, title FROM movies
JOIN stars ON movies.id = stars.movie_id
JOIN people ON stars.person_id = people.id
WHERE movies.id IN
(SELECT movies.id FROM movies
WHERE name = "Johnny Depp");
```

[hatena19]

なるほど。回答が間違ってましたね。
回答を修正しましたので、ご参照ください。

[gomashabu]

スッキリしました！
サブクエリ内に入ることが問題だったのではなく、JOINし直してから二つ目の条件を設定することが必要だったということですね。
ご回答ありがとうございました。