Which join is better peforming if all of them provides the same result?
For example, I have a two tables employees(emp_id,name, address, designation, age, sex)
and work_log(emp_id,date,hours_wored)
. To get some specific results both inner join
and left join
gives the same result. But, I have still some doubts which is not limited to this question only.
- which join is more efficient which should prefer in case of same result values ?
- What are other factors which must be considered at the time of applying join?
- Is there any relationship between inner join and cross join?
Best Answer
There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it.
In your case, you probably do not have employees with no
work_log
(no rows in that table), soLEFT JOIN
andJOIN
will be equivalent in results. However, if you had such a thing (a new employee with no registeredwork_log
), aJOIN
wold omit that employee, while a left join (whose first table is employees) would show all of them, and nulls on the fields fromwork_log
if there are not matches.Image by C.L. Moffatt on Code Project
Again, performance is a secondary thing to query correctness. Some people say that you shouldn't use
LEFT JOIN
s. It is true that aLEFT JOIN
forces the optimizer to execute the query in one particular order, preventing some optimizations (table reordering) in some cases. Here is one example. But you should not choose one over the other if correctness/meaning is sacrificed, as anINNER JOIN
is not inherently worse. The rest of the usual optimizations apply as usual.In summary, do not use
LEFT JOIN
if you really meanINNER JOIN
.In MySQL
CROSS JOIN
,INNER JOIN
andJOIN
are the same. In the standard, and semantically, aCROSS JOIN
is anINNER JOIN
without anON
clause, so you get every combination of rows between tables.You have examples of all semantic types of join on Wikipedia. In practice, in MySQL, we tend to only write
JOIN
andLEFT JOIN
.