[Optimizer] Rewrite `LEFT JOIN` to `INNER JOIN` When Filter Removes NULL-Extended Rows

[joocer]

trafficstars

REDUNDANCY RULE

We can rewrite an outer join to an inner join if the query contains a filter that would already reject fany null values (which is most filters)

SELECT s.id, p.id FROM $satellites AS s LEFT JOIN $planets AS p ON s.id = p.id WHERE p.id = 5

is the same as

SELECT s.id, p.id FROM $satellites AS s INNER JOIN $planets AS p ON s.id = p.id WHERE p.id = 5

Criteria for Rewrite

• Must be a LEFT JOIN
• There is a WHERE filter that excludes nulls from the right-hand side (most ops)

We can detect and rewrite in the optimizer