Re: LEFT JOIN vs INNER JOIN performance for the same amount of data returned

[Clint Byrum]

I think this is just the optimizer confusing you.

Essentially what you have on the left join, is that it must run the subquery into a temptable, then look at _every_ row in location_total, since it is on the left side, and scan the temp table created by the subquery to see if any rows match id_location and id_period. With the inner join, it can run the subquery, then just pluck out the matching rows in location_total using id_location and id_period, which are probably both indexed, since the rows must exist in both to be joined. Thats also why the select is the same (you should post the explain next time, so we can understand what the optimizer sees in your tables).

In your context, I'm not sure why you'd want to left join, since you only want to update the rows with totals right?. If you do need 0's for location_total's where there are no expense_out records, you could do a second update which updates those, or you could union inside your subselect to have all the 0's come last (union select 0 as total, x,y,z, from location_total left join expense_out using (id_location,id_period) where expense_out.primarykey is null).