Execution time taken by different SQL JOINS-CodePudding

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?

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";

CodePudding user response：

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.

CodePudding user response：

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.