Left join vs inner join - Which is better?
Which join is better performing if all of them provide the same result? For example, I have 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 still have some doubts which are not limited to this question only.
Which join is more efficient, which should I prefer in case of the same result values ? What are other factors which must be considered at the time of applying to join? Is there any relationship between inner join and cross join?
left join vs inner join
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.046sExplain 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