mysql - 将 SQL 语句中的 NOT IN 替换为 LEFT JOIN

Question

我写了两个运行良好的查询，但它们真的很慢:

SELECT director
FROM movies
WHERE id NOT IN (SELECT movie_id FROM stars_in_movies WHERER star_id = %s);

SELECT first_name, last_name
FROM stars
WHERE id NOT IN (SELECT star_id
                 FROM stars_in_movies
                 WHERE movie_id IN(SELECT movie_id
                                   FROM stars_in_movies
                                   WHERE star_id = %s))

我尝试将 NOT IN 替换为 INNER JOIN 和 LEFT JOIN 但到目前为止我没有尝试任何工作。

以下是表的架构:

电影:

 - id (primary key),
 - title (title of the movie), 
 - year (year of release)
 - director (director of the movie)

星星:

 - id (primary key)
 - first_name
 - last_name

stars_in_movies:

 - movie_id, 
 - star_id (movie_id and star_id both are foreign keys here)

提前谢谢你。

Answer 1

试试这个:

SELECT m.director 
FROM movies m
LEFT JOIN stars_in_movies sm ON m.id = sm.movie_id
WHERE sm.movie_id IS NULL 
AND sm.star_id = %s

第二个查询

  SELECT first_name, last_name
  FROM stars s
  LEFT JOIN stars_in_movies sm
  ON sm.star_id = s.id
  WHERE sm.star_id IS NULL
  AND sm.star_id = %s