Queries¶

General Points:

• Use as restrictive a LIKE pattern as possible, to avoid false matches. E.g. %(2010)% is anywhere in the title, while %(2010) is at the end of the title. And %2010% yields a false match: "2010: The Year We Make Contact (1984)"
• JOIN and GROUP BY an entity's ID, rather than name, title and the like. IDs exist for that reason.
• I often use "LIMIT" when I just want a sample of the output, but I would almost never use that in production code. In production code, we want usually want all the data

Q7 "movies without ratings"¶

Q7 was "movies without ratings"

Q7 was sometimes very (very slow). Here's a slow one:

select title 
from movie 
where title not in (
    select title
    from movie inner join rating using (movieId)
    group by title)
order by title;

That subquery is a lot of work, just to determine whether a movie has any ratings.

Here's one that is even slower:

select movie.title from movie,rating
where not exists (
    select rating.movieId from rating
    where rating.movieId = movie.movieId) 
order by title;

This one does a cross-product with the rating table, without any condition, so the result is millions of rows. It takes so long to run that I had to kill it. Avoid that.

Here's a query that runs much faster:

select title from movie
where movieid not in (select movieid from rating)
order by title;

Just grab a column from the rating table, and see if the movieId is in it. This is semantically equivalent and executes much faster.

Speed¶

In general, you should not think about efficiency and speed of execution. All the queries we'll do in this course are fast: less than a second. If anything runs longer than that, kill it and figure out what went wrong.

If, like Q7, the query optimizer has failed (rare, but not impossible), try to think about an equivalent query, often a simpler one.

I'm happy to help.