Главное различие — в том, что происходит с несовпавшими строками. Например, если есть список пользователей и список их заказов, INNER JOIN покажет только пользователей с заказами, а LEFT JOIN — всех пользователей, в том числе тех, у кого заказов нет.
INNER JOIN возвращает только те строки, для которых есть совпадение в обеих таблицах. LEFT JOIN возвращает все строки из левой таблицы, даже если соответствующей строки в правой таблице нет — тогда вместо её данных будет NULL.
INNER JOIN возвращает все строки из первой таблицы, а LEFT JOIN — только те, где найдено совпадение.