Optimizers: The Low-Key MVP

> The SQL above results in a plan similar to the DuckDB optimized plan, but it is wordier and more error-prone to write, which can potentially lead to bugs.

FWIW, aside from manual filter pushdown, I consider the JOIN variant the canonical / "default" way to merge multiple tables; it keeps all the join-related logic in one place, while mixing both joining conditions and filtering conditions in WHERE always felt more error-prone to me.

It is also the only way to represent join conditions for outer joins.

Same here; I've always intuited that this would limit the generated tuples. I'm too lazy to do it now, but I wonder if other DB engines also perform this optimization that effectively makes filtering in JOIN conditions equivalent to filtering in WHERE clauses. I'd also be interested in some example queries that were hand-optimized to the point of obvious obscurity--my guess is it's harder to do this in SQL than in something like C.

Any non-toy SQL engine is going to do condition pullup and pushdown. (Well, I know at least one that only does explicit pullup, but does pushdown more implicitly because it doesn't use a tree. The effect is largely the same, modulo some shenanigans around outer joins.)

Pushdown is actually a more subtle problem than people give it credit for; it inhabits this weird space where things are too trivial to be covered in papers but is too hard to cover properly in textbooks (e.g., no coverage of multiple equalities or outer joins).