Can we optimize this query?

Question

I was currently solving the Databases exercises on Coursera and encountered a problem.

Problem

You've started a new movie-rating website, and you've been collecting data on reviewers' ratings of various movies. There's not much data yet, but you can still try out some interesting queries. Here's the schema:

Movie ( mID, title, year, director ) 
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate )

Find the titles of all movies that have no ratings.

Solution 1

SELECT DISTINCT title
FROM Movie, Rating
WHERE Movie.mID NOT IN
(SELECT mID
FROM Rating)

Solution 2

SELECT DISTINCT title
FROM Movie, Rating
WHERE NOT EXISTS(SELECT * FROM Rating
WHERE Rating.mID = Movie.mID)

Solution 3

SELECT title
FROM Movie
WHERE title NOT IN(SELECT title
FROM Movie NATURAL JOIN Rating)

I wanted to know whether this can be optimised because I applied DISTINCT to make them run.

Raphaël Althaus · Accepted Answer · 2014-01-12T12:34:39.630

3

well, first, you don't need to select from Movie,Rating, as you don't do anything with Rating table.

And this is doing an horrible (and useless) cross join => cartesian product, which force you do make a Distinct.

so

select m.title
from Movie m
where not exists (select null -- or select *, doesn't change anything
                  from Rating r
                  where r.mId = m.mID)

you could also do something like that, but I think the previous one is clearer.

select m.title
from Movie m
left join Rating r on r.mId = m.mID
where r.rID is null

A NOT IN query (without join on Rating) is also perfectly valid, but probably the one with poorest performance, and would be written this way.

select m.title 
from Movie m
where m.mId not in (select distinct mID from Rating)

edited Jan 12 '14 at 12:34

answered Jan 12 '14 at 12:29

Raphaël Althaus

59,727
6
96
122

Thanks for response. I have added another solution 3. Can you please comment on that whether it is better than others. – Sumit Gera Jan 12 '14 at 12:33
@mozart yes, added a NOT IN version in answer – Raphaël Althaus Jan 12 '14 at 12:34
Oh thanks. Is it because `NOT IN` checks for all the entries in the subquery for every `Movie` tuple? – Sumit Gera Jan 12 '14 at 12:36
1

@mozart see http://stackoverflow.com/questions/173041/not-in-vs-not-exists or http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null if that's your question – Raphaël Althaus Jan 12 '14 at 12:38

score 0 · Answer 2 · answered Jan 12 '14 at 12:29

0

Including Rating in the outer FROM is not needed, without it you can avoid DISTINCT.