SQLite Forum

problem report: incorrect join behavior
See [this optimization](https://www.sqlite.org/optoverview.html#leftjoinreduction)

> A LEFT JOIN can sometimes be converted into an ordinary JOIN if there are terms in the WHERE clause that guarantee that the two joins will give identical results. In particular, if any column in the right-hand table of the LEFT JOIN must be non-NULL in order for the WHERE clause to be true, then the LEFT JOIN is demoted to an ordinary JOIN.

In your case the ON condition only references the left table, so there's no point in it being an outer join, and thus it treats it as an inner join.