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

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

Following is schema for the tables:

movies:

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

stars:

 - id (primary key)
 - first_name
 - last_name

stars_in_movies:

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

Thank you in advance.

Answer

Try this:

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

the second query

  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

Advertisement

Answer