For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.