0

From my limited understanding, JOIN operations are supposed to generally be faster than nested subqueries. However, when solving the various Movies SQL problems, I'm finding queries with JOINs (especially with 3+ tables) to be grossly slower than nested queries. A simple example, for problem 11:

SELECT m.title
FROM stars s JOIN people p ON s.person_id=p.id
    JOIN movies m ON m.id=s.movie_id
    JOIN ratings r ON r.movie_id=m.id
WHERE p.name="Chadwick Boseman"
ORDER BY r.rating DESC
LIMIT 5;

Using nested subqueries produces the same result:

SELECT m.title
FROM movies m JOIN ratings r ON m.id=r.movie_id
WHERE m.id IN
    (SELECT movie_id FROM stars WHERE person_id=
        (SELECT id FROM people WHERE name="Chadwick Boseman"))
ORDER BY r.rating DESC
LIMIT 5;

But the second one is executing consistently about 12-15 times faster when run locally. Most documentation seems to indicate that for SQL queries in general, things like the JOIN order shouldn't really matter, and various arrangements of the query don't seem to make any different.

Is this a limitation of SQLite or am I doing something fundamentally wrong?

You must log in to answer this question.

Browse other questions tagged .