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?