Further optimization of subqueries with comparators

[janetzki]

SELECT c_custkey
FROM customer
WHERE c_acctbal < (
  SELECT MAX(o_totalprice)
  FROM orders
  WHERE o_custkey = c_custkey
)

returns all customers who have at least one order but cannot afford any of them.
We currently optimize it to an inner join that has c_acctbal < o_totalprice as its predicate.
However, the query could also be written in an even more efficient way that uses an equi join:

SELECT c_custkey
FROM customer
JOIN orders ON o_custkey = c_custkey
GROUP BY c_custkey, c_acctbal
HAVING c_acctbal < MAX(o_totalprice)

Specifically for this case, it is possible to boost it even further:

SELECT DISTINCT c_custkey
FROM customer
JOIN orders ON o_custkey = c_custkey
WHERE c_acctbal < o_totalprice

The SubqueryToJoinRule should apply these reformulation techniques when possible.