mysql - MySQL inner join changes column value

Question

I found something really confusing recently about inner join:

select records.id, 
       records.amount, 
       records.payment_amount, 
       orders.id as or_id 
from `records` 
**`inner`** `join order_from_site orders on records.id = orders.`record_id` 

will somehow set the payment_amount value to 0.0.

If however, I change the inner join to left join, the value is correctly preserved. Not sure if this is the expected behavior or a bug?

I'm using MySQL 8.0.

标签： mysqlleft-joininner-join

Answer 1

Your question does not provide any data from the two tables so it will be hard to determine the answer to your question without the data.

That said, an inner join requires matched data from both sides to be present in order to display a row. A left join requires the table from the left to be present and only optionally on the right.