Add a rule to remove the null-generating side of a Join. Here is an example where we eliminate the "many" side of a "one-to-many" join:
We can remove SupportCalls and the join to it, so the query becomes
Necessary conditions are:
- no columns from SupportCalls are used
- the join is LEFT, so customers will not be eliminated if they have no support calls,
- there is an Aggregate on top, so we don't care if there are >1 support call per customer.
A simpler example of one-to-many:
An example of many-to-one, where we eliminate the "one" side (Orders):
so that the query becomes
Here, necessary side-conditions are:
- no columns from Orders are used;
- unique key on Orders.id.
We do not require aggregation, because the primary key on Orders.id ensures that Orders contributes at most one row.
We can deal with similar cases like
if we use aliasing (o.id = i.orderId) and a foreign key that ensures the existence of an record in Orders.
For examples 1 and 2 (one-to-many), we would need to match Aggregate on Join, therefore make a variant of AggregateJoinTransposeRule.
For examples 3 and 4 (many-to-one or one-to-one)), we would match Project on Join, therefore make a variant of ProjectJoinTransposeRule.