At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
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.)
With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.
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.