Execution time taken by different SQL JOINS

Question

Below two queries result the same result set. In first I have only used INNER JOIN and in second query mix of joins like LEFT and RIGHT JOIN. I personally prefer INNER JOIN when there is no specific task/requirement of other joins. But I just want to know that is there any difference between the below two queries in terms of performance or execution time. Is it ok to use inner join than the mix of joins?

1.

SELECT film.title, category.name, film.rating, language.name
  FROM film INNER JOIN film_category ON film_category.film_id = film.film_id
            INNER JOIN category ON category.category_id = film_category.category_id
            INNER JOIN language ON language.language_id = film.language_id
            WHERE category.name = "Sci-Fi" AND film.rating = "NC-17";

SELECT film.title, film.release_year, film.rating,category.name, language.name 
FROM film LEFT JOIN language ON language.language_id=film.language_id 
          RIGHT JOIN film_category ON film_category.film_id = film.film_id 
          LEFT JOIN category ON category.category_id=film_category.category_id  
          WHERE film.rating="NC-17" AND category.name="Sci-Fi";

The first query is the most performant and the recommended one. They may bring the same results depending on the data but they are not logically equivalent. In addition to that, the second one is definitely confusing and hard to read. I do not recommend using this kind of syntax for production code! — Cristian Rusanu, Sep 18 '22 at 17:28
I suggest that it doesn't matter if an INNER JOIN runs faster or slower than LEFT JOIN. It's like asking if multiplication is faster than subtraction. They do different things, so you use the one that is correct for your situation. If you're asking if you can shave off a few milliseconds by using the incorrect type of JOIN, please don't think that way. — Andy Lester, Sep 18 '22 at 18:02
Don't use inner join and left join interchangeably even if they yield the same result for a specific dataset. Use the join that gets you the right result AND also communicates the intentions/assumptions of your code/dataset. Also, outside of school, right joins are used quite rarely because they can be easily rewritten using a left join, which are way more widely used. You should learn them, but I wouldn't bother finding ways to use right joins at work. — Radagast, Sep 20 '22 at 00:07

score 1 · Answer 1 · answered Sep 18 '22 at 17:24

1

Please see this INNER JOIN vs LEFT JOIN performance in SQL Server.

However, choosing the proper join type is depending on the usecase and result set which you need to extract.

answered Sep 18 '22 at 17:24

Abishek VK

506
3
12

1

this is more a comment than an answer, also it is tagged mysql – nbk Sep 18 '22 at 17:49

score 0 · Answer 2 · answered Sep 19 '22 at 23:29

Please do not mix the different types except in this way: INNER, INNER, ... LEFT, LEFT, ... Any other combination has ambiguities about what gets done first. If you must mix them up, use parentheses to indicate which JOIN must be done before the others.

As for whether INNER/LEFT/RIGHT are identical, let me explain with one example:

SELECT ...
    FROM a
    LEFT JOIN b ON ...  -- really INNER
    WHERE b.x = 17

That WHERE effectively turns the LEFT JOIN into INNER JOIN. The Optimizer will do such. I, as a human, will stumble over the query until I realize that. So, humor me by calling it INNER JOIN.

Phrased another way, use LEFT only when the "right" table's columns are optional, but you want NULLs when they are missing. Of course, you may want the NULLs so you can say "find rows of a that are not in b:

SELECT ...
    FROM a
    LEFT JOIN b ON ...
    WHERE b.id IS NULL   -- common use for LEFT

While I have your attention, here are some notes/rules:

The keywords INNER, CROSS, and OUTER are ignored by MySQL. The ON and WHERE clauses will determine which type of JOIN is really intended.
Have you ever seen an owl turn its head nearly all the way around? That's what happens to my head when I see a RIGHT JOIN. Please convert it to a LEFT JOIN.
Though the Optimizer does not require this distinction, please use ON to specify how the tables are related and use WHERE for filtering. (With INNER, they are equivalent; with LEFT, you may get different results.)
Sometimes EXISTS( SELECT ... ) is better than a LEFT JOIN.
Optimizations vary depending on the existence of GROUP BY, ORDER BY, and LIMIT. But that is a loooong discussion.

Back to your question of which is faster, etc. Well, if the Optimizer is going to turn one into another, then those two have identical performance.

Execution time taken by different SQL JOINS

2 Answers2