MySQL: Which join is better between left outer join and inner join

Question

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?

Answer 1

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), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that employee, while a left join (whose first table is employees) would show all of them, and nulls on the fields from work_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 JOINs. It is true that a LEFT 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 an INNER JOIN is not inherently worse. The rest of the usual optimizations apply as usual.

In summary, do not use LEFT JOIN if you really mean INNER JOIN.

In MySQL CROSS JOIN, INNER JOIN and JOIN are the same. In the standard, and semantically, a CROSS JOIN is an INNER JOIN without an ON 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 and LEFT JOIN.

Answer 2

Which join is better performing if all of them provides the same result?

I addition of the previous answer, for what I know, MySQL is optimized to have the same performance.

With good indexes, for example, JOIN vs LEFT JOIN + WHERE clause to filter, will be the same thing. Optimization vs Human Reading take sense on large queries with lot of joins.

Using good indexes and cache is more important.

You can read a good explanation of the optimization process here :

The Query Optimization Process : A query can often be executed many different ways and produce the same result. The optimizer’s job is to find the best option.

Answer 3

It's not the same for me, MySql 5.7 8 vCPU, 52 GB RAM

The following query takes ~30 seconds, not sure why

The transactions table has 24,257,151 records

The activity table has 18,603,665 records

The purchases table has 13,911,705 records

All required indexes are in place

SELECT
    `trx`.`transaction_pk`,
    `trx`.`created`,
    `trx`.`updated`,
    `p`.`amount`,
    `trxst`.`name`,
    COALESCE ( a.units, 0 ) AS units
FROM
    `transaction` AS `trx`
    INNER JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`
    left JOIN `activity` AS `a` ON `a`.`transaction_fk` = `trx`.`transaction_pk`
    LEFT JOIN `purchases` AS `p` ON `p`.`transaction_fk` = `trx`.`transaction_pk` 
WHERE
    `trx`.`entity_fk` IN ( 1234) 
    AND `trx`.`transaction_sub_type_fk` IN (
    2, 4, 5, 15, 16, 33, 37, 38, 85, 86, 87, 88, 102, 103 
    ) 
ORDER BY
    `trx`.`transaction_pk` DESC LIMIT 100 OFFSET 0;

After replacing the following line:

INNER JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`

with LEFT JOIN

LEFT JOIN `transaction_sub_type` AS `trxst` ON  `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`

the same query takes ~0.046s

Explain before:

1   SIMPLE  trxst       ALL PRIMARY             101 37.62   Using where; Using temporary; Using filesort
1   SIMPLE  trx     ref transaction_sub_type_fk,entity_fk   transaction_sub_type_fk 4   trxst.transaction_sub_type_pk   2548    0.36    Using where
1   SIMPLE  a       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100 
1   SIMPLE  p       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100 

Explain after:

1   SIMPLE  trx     ref transaction_sub_type_fk,entity_fk   entity_fk   4   const   81474   83.65   Using where
1   SIMPLE  trxst       eq_ref  PRIMARY PRIMARY 4   trx.transaction_sub_type_fk 1   100 
1   SIMPLE  a       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100 
1   SIMPLE  p       ref transaction_fk  transaction_fk  4   trx.transaction_pk  1   100