在SQL语句中将NOT IN替换为LEFT JOIN [英] Replace NOT IN with LEFT JOIN in SQL statement

问题描述

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

I wrote two queries which are working fine, but they are really really slow:

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))

我试图用INNER JOIN和LEFT JOIN替换NOT IN，但是到目前为止我没有尝试过.

I tried to replace NOT IN with INNER JOIN and LEFT JOIN but nothing I have tried worked so far.

以下是表的架构:

电影:

 - 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)

谢谢.

推荐答案

尝试一下:

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 star_id = %s

这篇关于在SQL语句中将NOT IN替换为LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！